Solved

SQL server stored procedure syntax

Posted on 2009-07-13
15
846 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
  • 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
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 250 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

762 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