Post reply

Placeholders in EXEC statements

  • March 7, 2007 at 10:39 am

    #72636

    Does MS SQL Server support placeholders being used in EXEC statements?  I have a Perl DBI application (that fires EXEC statements aginst my reception SPROC) that I want to use placehoders with.  I am having difficulty with it as it gives me the error message: 

    Syntax error converting the nvarchar value 'x' to a column of data type int.

  • March 7, 2007 at 11:49 am

    #693635

    because you are doning dynamic SQL, you'll want to put the SQL command in a string with a placeholder, and then replace the placeholder prior to the real exec statement. something like this:

    declare

    @isql varchar(2000),

    @dbname varchar(64)

    SELECT @dbname =somedb from sometable

    select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'

    select @isql = replace(@isql,'@dbname',@dbname)

    print

    @isql

    exec(@isql

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 8, 2007 at 2:07 am

    #693735

    Sounds interesting.  But I'm not sure I understand what you are showing me.  I suppose the questions that I originally posted should have been: Firstly, does MS SQL Server support placeholders of the kind that Perl DBI use.  Secondly, if so, do they apply to EXEC statements?  If the answer to both of these is no.  What alternative solutions could be applied at the T-SQL level? 

  • March 8, 2007 at 3:50 am

    #693759

    I'm not totally sure what you're asking ( never used Perl ) but you might want to check out sp_executesql in BOL.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply