Post reply

Need help on dynamic SQL

  • May 19, 2010 at 8:23 am

    #139084

    Let's say, my SQL as follow,

    declare @tblnme varchar(30)

    set @tblnme='sysrunno'

    declare @sql nvarchar(4000)

    declare @maxno int

    set @sql='select @maxno=max(runno) from ' + @tblnme

    EXEC sp_executesql @sql

    I want my dynamic SQL set max(runno) to @maxno. my statement above return error

  • May 19, 2010 at 8:38 am

    #1169473

    It might help to say what the error is

    I think you need to declare @maxno inside the dynamic sql

  • May 19, 2010 at 8:49 am

    #1169484

    tq sir. your guidance is my inspiration

  • May 19, 2010 at 9:27 am

    #1169509

    You can use homebrew01's idea, but then there is a catch there; when u declare it inside the dynamic sql, your code which used "maxno" has to be packed up with inside. Instead, u can make use of the output parameter in sp_executesql

    like:

    DECLARE @IntVariable int;

    DECLARE @SQLString nvarchar(500);

    DECLARE @ParmDefinition nvarchar(500);

    DECLARE @max_title varchar(30);

    SET @IntVariable = 197;

    SET @SQLString = N'SELECT @max_titleOUT = max(Title)

    FROM AdventureWorks.HumanResources.Employee

    WHERE ManagerID = @level';

    SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

    EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;

    SELECT @max_title;

    Hope this helps you!

    Cheers! 😎

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

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