Solved

SQL server stored procedure syntax

Posted on 2009-07-13
15
850 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

739 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