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
7
Medium Priority
?
1,377 Views
Last Modified: 2008-02-20
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
Comment
Question by:mk_b
  • 4
  • 2
7 Comments
 
LVL 8

Expert Comment

by:sankarbha
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

by:
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

instead of

N'@shipping varchar(100) out'

HTH

Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13823551
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Author Comment

by:mk_b
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

by:Hilaire
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

by:mk_b
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

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

instead of

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

 (all quotes are single quotes)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question