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
LVL 8
newbie27Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.