Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 878
  • Last Modified:

SQL server stored procedure syntax

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
newbie27
Asked:
newbie27
  • 7
  • 7
1 Solution
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Huh what was the question again?
0
 
RiteshShahCommented:
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
 
RiteshShahCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
newbie27Author Commented:
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
 
newbie27Author Commented:
Thanks.

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

I am getting INSERT statement error

Please advise

thanks
0
 
newbie27Author Commented:
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
 
newbie27Author Commented:
correction :


It seems I am NOT * getting the ID from the statement
0
 
RiteshShahCommented:
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
 
newbie27Author Commented:
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
 
RiteshShahCommented:
yes, syntax seems ok to me.
0
 
RiteshShahCommented:
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
 
newbie27Author Commented:
That seems to have done the trick RiteshShah !!

Thank you so much for your help !!
0
 
RiteshShahCommented:
glad to help!!!

Ritesh Shah

www.SQLHub.com
0
 
newbie27Author Commented:
Nice Blog and ofcourse nice pic (:-
0
 
RiteshShahCommented:
thanks for the complement
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now