Solved

SQL server stored procedure syntax

Posted on 2009-07-13
15
835 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

867 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