i got error in stored procedure

Posted by Kannan1986 under Sql Server on 3/3/2010 | Views : 1172 | Status : [Member] | Replies : 2
Hi,

I got error in sql stored procedure...


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [bigsaas].[sp_shipping_method_update]

@name varchar(50),
@description varchar(max),
@disp_template varchar(50),
@url_tracker_template varchar(50),
@enabled char(10),
@sort int,
@uss varchar(150)
as
declare @valu varchar(150)

begin

if exists ('select name from' +@uss+ '_Shipping_Method' + 'where name = @name')
set @valu = 'update' +@uss+ '_Shipping_Method' + 'set name = @name , description = @description ,
disp_template = @disp_template , url_tracker_template = @url_tracker_template ,
enabled = @enabled , sort = @sort where name= @name'
else
set @valu = 'insert into ' +@uss+'_Shipping_Method' + 'values (@name,@description,@disp_template,@url_tracker_template,@enabled,@sort)'

exec @valu

end


error message

Msg 102, Level 15, State 1, Procedure sp_shipping_method_update, Line 16
Incorrect syntax near 'select name from'.
Msg 156, Level 15, State 1, Procedure sp_shipping_method_update, Line 20
Incorrect syntax near the keyword 'else'.




Responses

Posted by: Abhi2434 on: 3/4/2010 [Member] [Microsoft_MVP] [MVP] Silver

Up
0
Down
Well
the problem is If Exists expects an SQL not a string. You passed a dynamic sql as string, which cannot be parsed.

What you should do, is either pass
If exists(SELECT NAME FROM USERS WHERE NAME =@name)

or you use

'SELECT COUNT(name) from ' @uss

And check the value.

www.abhisheksur.com

Kannan1986, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gttejas on: 3/18/2010 [Member] Starter

Up
0
Down
Hi,

I think yes Abhi is right. it accepts only query not a string.

Please try to execute string using so Exists(exec(string))


Please mark it as ans. if it solve your problem.




Kannan1986, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response