?
Solved

SQL server stored procedure syntax

Posted on 2009-07-13
15
Medium Priority
?
865 Views
Last Modified: 2012-05-07
Hello Experts,

With regards to my previous question, I am now having no values being returned in @PC_PatternID variable.

Please can you advise if I am doing something wrong in my SP?

thanks
s
0
Comment
Question by:newbie27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24839326
Huh what was the question again?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24839341
define @pc_patternID as output parameter like this
ALTER PROCEDURE [dbo].[procRegisterPattern] 
        -- Add the parameters for the stored procedure here     
@PC_ID                  INT,    
@PC_PatternName         CHAR(100) output,      
@PC_Value               CHAR(100)
AS
BEGIN   
-- SET NOCOUNT ON added to prevent extra result sets from       
-- interfering with SELECT statements.  
SET NOCOUNT ON; 
DECLARE @PC_PatternID int       
SET @PC_PatternID = -1  
SET @PC_PatternID = (SELECT PC_PatternID FROM PC_Pattern WHERE [Name] LIKE @PC_PatternName)   -- Error at this line
        IF @PC_PatternID < 0 BEGIN
                SET @PC_PatternID = (INSERT INTO PC_Pattern([Name]) VALUES (@PC_PatternName) SELECT @@IDENTITY)  --- and this line
        END     
INSERT INTO PC_Match(PC_ID, PC_PatternID, PC_Value)     
VALUES (@PC_ID, @PC_PatternID, @PC_Value)
END
 
 
 
Incorrect syntax near the keyword 'INSERT'.
Incorrect syntax near ')'.

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24839393
you can call your SP like this.

declare @n char(100)
exec [dbo].[procRegisterPattern]  @pc_id=1,@pc_patternName=@n output,@pc_value ='any value you have'
select @n --value will be returned to @n
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 8

Author Comment

by:newbie27
ID: 24839406
Hi RiteshShah

Thanks for your input. I dont want to return anything back from this SP.

All I want to do is to get the Patter_ID matching the Pattername from the PC_Pattern table and insert into the PC_Match

I am not getting correct value for PC_PatternID in the INSERT statement below

INSERT INTO PC_Match(PC_ID, PC_PatternID, PC_Value)    
VALUES (@PC_ID, @PC_PatternID, @PC_Value)

Instead of adding
id    pat_id   value
10     1           Alice

its adding

10     19        Alice

19 : I believe is the number for the total number of records being added to the table...

I hope you understand what I am trying to say here?

thanks
s
ALTER PROCEDURE [dbo].[procRegisterPattern] 
        -- Add the parameters for the stored procedure here     
@PC_ID                  INT,    
@PC_PatternName         VARCHAR(100),      
@PC_Value               VARCHAR(100)
AS
BEGIN   
-- SET NOCOUNT ON added to prevent extra result sets from       
-- interfering with SELECT statements.  
SET NOCOUNT ON; 
DECLARE @PC_PatternID int       
 
SELECT @PC_PatternID = PC_PatternID 
  FROM PC_Pattern 
 WHERE [Name] LIKE @PC_PatternName
 
IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO PC_Pattern([Name]) VALUES (@PC_PatternName) 
  SET @PC_PatternID = SCOPE_IDENTITY()
END     
INSERT INTO PC_Match(PC_ID, PC_PatternID, PC_Value)     
VALUES (@PC_ID, @PC_PatternID, @PC_Value)
END

Open in new window

0
 
LVL 8

Author Comment

by:newbie27
ID: 24839444
Thanks.

I have tried to test using your suggested Output parameter systax ...

I am getting INSERT statement error

Please advise

thanks
0
 
LVL 8

Author Comment

by:newbie27
ID: 24839760
Hi,

It seems I am getting the ID from the statement

SELECT @PC_PatternID = PC_PatternID
  FROM PC_Pattern
 WHERE [Name] LIKE @PC_PatternName

although the given name already exist in the pc_pattern name...

please can someone advise?

thanks
s
0
 
LVL 8

Author Comment

by:newbie27
ID: 24839771
correction :


It seems I am NOT * getting the ID from the statement
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24839903
you have used like in your SP but didn't use any wildcard character. can't you do something like this?



ALTER PROCEDURE [dbo].[procRegisterPattern] 
        -- Add the parameters for the stored procedure here     
@PC_ID                  INT,    
@PC_PatternName         VARCHAR(100),      
@PC_Value               VARCHAR(100)
AS
BEGIN   
-- SET NOCOUNT ON added to prevent extra result sets from       
-- interfering with SELECT statements.  
SET NOCOUNT ON; 
DECLARE @PC_PatternID int       
 
SELECT @PC_PatternID = PC_PatternID 
  FROM PC_Pattern 
 WHERE [Name] LIKE '%' + @PC_PatternName + '%'
 
IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO PC_Pattern([Name]) VALUES (@PC_PatternName) 
  SET @PC_PatternID = SCOPE_IDENTITY()
END     
INSERT INTO PC_Match(PC_ID, PC_PatternID, PC_Value)     
VALUES (@PC_ID, @PC_PatternID, @PC_Value)
END

Open in new window

0
 
LVL 8

Author Comment

by:newbie27
ID: 24839998
Hi

Thanks again, OK I have tried with the wild cards as suggested above,

SELECT @PC_PatternID = PC_PatternID
  FROM PC_Pattern
 WHERE [Name] LIKE '%' + @PC_PatternName + '%'


Because its not returning any result, the following statements is getting executed...

IF @@ROWCOUNT = 0

Is the above syntax is correct to assign the value to @PC_PatternID ?

Please advise

thanks
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24840011
yes, syntax seems ok to me.
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 1000 total points
ID: 24840035
or you can do something like this as if there is nothing return from your query, there will be something <0 in your patternID.
ALTER PROCEDURE [dbo].[procRegisterPattern] 
        -- Add the parameters for the stored procedure here     
@PC_ID                  INT,    
@PC_PatternName         VARCHAR(100),      
@PC_Value               VARCHAR(100)
AS
BEGIN   
-- SET NOCOUNT ON added to prevent extra result sets from       
-- interfering with SELECT statements.  
SET NOCOUNT ON; 
DECLARE @PC_PatternID int       
set @PC_PatternID=-1
SELECT @PC_PatternID = PC_PatternID 
  FROM PC_Pattern 
 WHERE [Name] LIKE '%' + @PC_PatternName + '%'
 
IF @PC_PatternID <= 0
BEGIN
  INSERT INTO PC_Pattern([Name]) VALUES (@PC_PatternName) 
  SET @PC_PatternID = SCOPE_IDENTITY()
END     
INSERT INTO PC_Match(PC_ID, PC_PatternID, PC_Value)     
VALUES (@PC_ID, @PC_PatternID, @PC_Value)
END

Open in new window

0
 
LVL 8

Author Comment

by:newbie27
ID: 24840259
That seems to have done the trick RiteshShah !!

Thank you so much for your help !!
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24840760
glad to help!!!

Ritesh Shah

www.SQLHub.com
0
 
LVL 8

Author Comment

by:newbie27
ID: 24842921
Nice Blog and ofcourse nice pic (:-
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24846062
thanks for the complement
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question