Solved

SQL server stored procedure syntax

Posted on 2009-07-13
15
834 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now