Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Set @shipping = exec(@sql)

Posted on 2005-04-20
Medium Priority
1,377 Views
Hello i have a problem with a little piece of code the error is in the line "Set @shipping = exec(@sql)" ERROR:Server: Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'exec'.

Set @weight = CEILING(@weight)

Declare
@i int
Set @i = 1

While @i < 31
Begin
If @weight = @i Begin
Set @tmp = @i
Set @tmp = @tmp + 'Kg'
Set @sql = '(Select ['+ @tmp +'] as shipping from speedZoneSA where ID=1)'
Set @shipping = exec(@sql)
End
Set @i = @i + 1
End

I need the var @shipping to do more calculations on it.

thanks
mk_bt
0
Question by:mk_b
• 4
• 2

LVL 8

Expert Comment

ID: 13823495
You can achieve this by using a temp table like this:

create table #tblTemp(shipping bigint)

Set @weight = CEILING(@weight)

Declare
@i int
Set @i = 1

While @i < 31
Begin
If @weight = @i Begin
Set @tmp = @i
Set @tmp = @tmp + 'Kg'
Set @sql = '(insert into #tblTemp Select ['+ @tmp +'] as shipping from speedZoneSA where ID=1)'
select @shipping = shipping from #tblTemp
delete from #tblTemp
......
End
Set @i = @i + 1
End

Otherwise you can create a sp (say spSetShipping)which returns the value of

exec('Select ['+ @tmp +'] as shipping from speedZoneSA where ID=1')

exec @shipping = spSetShipping @tmp
0

LVL 26

Accepted Solution

Hilaire earned 1600 total points
ID: 13823530
Hi,
You'll have to use sp_executesql instead of exec(@sql)

-- make sure @sql is declared as NVARCHAR, not varchar

Set @sql = N'Select @shipping = ['+ @tmp +N'] from speedZoneSA where ID=1'
exec sp_executesql @sql, N'@shipping varchar(100) out', @shipping out

also make sure the datatype of the variable declared in the second parameter of sp_executesql is consistent with the actual datatype of the destination variable

eg if @shipping is currently declared as char(10), use

N'@shipping char(10) out'    as a second parameter of the sp

N'@shipping varchar(100) out'

HTH

Hilaire
0

LVL 26

Expert Comment

ID: 13823551
0

LVL 5

Author Comment

ID: 13823576
thatnks that worked great please can you explain what

Set @sql = N'Select @shipping = ['+ @tmp +N'] from speedZoneSA where ID=1'
exec sp_executesql @sql, N'@shipping varchar(100) out', @shipping out

is and what the N if for?

regards,
mk_bt
0

LVL 26

Expert Comment

ID: 13823683
both exec(@sql) and sp_execute @sql are used to execute dynamic sql.

normally dynamic sql creates its own scope of execution and does not "see" the variables declared in the parent environment

with sp_execute sql you can execute sql and "pass" a local variable by reference to the child environment. That's why it's so important to have consistent datatypes in the 2nd param of sp_executeSQL and in the parent T-SQL.

N is a prefix for Nvarchar strings ,and sp_executesql expects parameters of nvarchar datatype (it does not convert on the fly since it works with references/pointers)

the 'string' string constant is implicitly declared as varchar,
the N'string' string constant is implicitly declared as Nvarchar

NVARCHAR = unicode strings, stored internally with 2 bytes per character

HTH
Hilaire

0

LVL 5

Author Comment

ID: 13824841
Hi Hilaire,

please could i ask you for one last thing im getting this ERROR:
Select @shipping = [kg2Half] from speedZone where (zone=A)
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'A'.

but its pulling the @fuelS correctly

Set @i = 0.5
Set @x = 0
While @i <= 20
Begin
If @weight <= @i and @weight > @x Begin
--Set @tmp = @i
Set @tmp = 'kg2Half'

Set @sql = N'Select @shipping = ['+ @tmp +N'] from speedZone where (zone='+ @Zone +N')'
Print @sql
Set @fuelS = (Select fuelSurcharge from speedZone where (zone=@Zone))
exec sp_executesql @sql, N'@shipping float out', @shipping out
End
Set @i = @i + 0.5
Set @x = @x + 0.5
End

regards,
Mark
0

LVL 26

Expert Comment

ID: 13824913
use
Set @sql = N'Select @shipping = ['+ @tmp +N'] from speedZone where (zone='''+ @Zone +N''')'

Set @sql = N'Select @shipping = ['+ @tmp +N'] from speedZone where (zone='+ @Zone +N')'

(all quotes are single quotes)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
###### Suggested Courses
Course of the Month11 days, 20 hours left to enroll