I got error in sql stored procedure

kannan s

16y2.9k

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)
--declare @cntt int
begin
--set @valu=
--set @cntt=(exec (@valu))
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'.

Thanks & Regards,

Kannan.S

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

kannan s16y ago
Hi,

Thanks... I solved the problem....
Amit Choudhary16y ago
hi friend,

Change your query

if exists(' select name from '+ @uss + '_Shipping_Method' + 'where name = @name')

to

if exists(' select name from @uss _Shipping_Method where name = @name')

Hope it resolves your error.

Don't forget to mark as answer.
kannan s16y ago
ok..thanks..but.. your code also making the same error...

is there any way to solve this problem....

Thanks & Regards,

kannan.S
shyamala16y ago
u didn't give proper begin and end.. and remove single quotes try this
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)
--declare @cntt int
--set @valu=
--set @cntt=(exec (@valu))
if exists(' select name from '+ @uss + '_Shipping_Method' + 'where name = @name')
begin

    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'
end
else
begin
    set @valu = 'insert into ' +@uss+'_Shipping_Method' + 'values (@name,@description,@disp_template,@url_tracker_template,@enabled,@sort)'
exec @valu

end
kannan s16y ago
ok... but... the error is in the if ...else.. condition.. i closed all brackets...

could you explain....
shyamala16y ago

Try like this your mistake is u gave single quotes('select name from')and ('insert into')

CREATE procedure [dbo].[Nsp_UpdateCustomerAddress]
(
@aid int,
@CustomerID int,
@Alias varchar(255),
@FirstName varchar (255),
@LastName varchar (255),
@Address1 varchar(1000),
@Address2 varchar(1000),
@City varchar(100),
@State varchar(100),
@ZipCode int,
@Country varchar(100),
@Phone varchar (20)
)
as
SET NOCOUNT ON
declare @cnt as bit
set @cnt = 0
if exists
(
select CustomerAddressID from CustomerAddress
where CustomerID = @CustomerID and CustomerAddressID = @aid
)
begin
update CustomerAddress
set
Alias = @Alias,
FirstName = @FirstName,
LastName = @LastName ,
Address1 = @Address1,
Address2 = @Address2,
City = @City,
State = @State,
ZipCode = @ZipCode,
Country = @Country,
Phone = @Phone
where CustomerID = @CustomerID and CustomerAddressID = @aid
set @cnt = 1
end

return @cnt