Define a SQL variable with Database Name

Question

With this T-SQL query I define a variable @InDate with some date.

SET 
    @InDate = (SELECT MIN(bt.CreateDate) AS [INDATE]
               FROM  
                   mydatabase.dbo.BuyTransaction bt
               LEFT JOIN 
                   mydatabase.dbo.TransactionExtraFields tef ON bt.TransDocument = tef.TransDocument 
                                                             AND bt.TransSerial = tef.TransSerial 
                                                             AND bt.TransDocNumber = tef.TransDocNumber 
                                                             AND ExtraFieldID = 1
               WHERE 
                   bt.TransDocument = 'FRM' AND tef.TextAnswer = @NumPI 
                   AND bt.TransStatus = 0)

But now I need to change the Database name by another variable. I try something like that to make a string in a variable which will be executed

SET @InDate = '(SELECT MIN(bt.CreateDate) AS [INDATE]
FROM mydatabase.dbo.BuyTransaction bt
LEFT JOIN mydatabase.dbo.TransactionExtraFields tef ON bt.TransDocument = tef.TransDocument AND bt.TransSerial = tef.TransSerial AND bt.TransDocNumber = tef.TransDocNumber AND ExtraFieldID = 1
WHERE bt.TransDocument = ''FRM'' AND tef.TextAnswer = '''+@NumPI+''' AND bt.TransStatus = 0)'

EXECUTE @InDate

But my problem is how can I get the execute result into a variable again. Something like SET @InDate2 = EXECUTE @InDate is not working...

Any idea?

Can yoy try EXEC(@InDate)

Jithin Shaji
– Jithin Shaji

2014-05-24 14:49:07 +00:00
Commented May 24, 2014 at 14:49 — Jithin Shaji
– Jithin Shaji, Commented May 24, 2014 at 14:49

Gordon Linoff · Accepted Answer · 2014-05-26 14:25:42Z

1

Use sp_executesql. I think the syntax in your case is something like:

declare @InDate date;
declare @sql nvarchar(max);

set @sql = N'SELECT @InDate = MIN(bt.CreateDate)
FROM mydatabase.dbo.BuyTransaction bt
LEFT JOIN mydatabase.dbo.TransactionExtraFields tef ON bt.TransDocument = tef.TransDocument AND bt.TransSerial = tef.TransSerial AND bt.TransDocNumber = tef.TransDocNumber AND ExtraFieldID = 1
WHERE bt.TransDocument = ''FRM'' AND tef.TextAnswer = '''+@NumPI+''' AND bt.TransStatus = 0';

exec sp_executesql @sql, N'@InDate date output', @InDate = @InDate output;

You can also make @NumPI a parameter as well.

The documentation for sp_executesql is here.

edited May 26, 2014 at 14:25

answered May 24, 2014 at 12:38

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

PJLG Over a year ago

Thank you @Gordon. My result is null, but I think I'm almost there! I try this:

SET @SQLString = N'SELECT @InDate = MIN(bt.CreateDate) FROM '+@myDB+'.dbo.BuyTransaction bt LEFT JOIN '+@myDB+'.dbo.TransactionExtraFields tef ON bt.TransDocument = tef.TransDocument AND bt.TransSerial = tef.TransSerial AND bt.TransDocNumber = tef.TransDocNumber AND ExtraFieldID = 1 WHERE bt.TransDocument = ''FRM'' AND tef.TextAnswer = '''+@NumPI+''' AND bt.TransStatus = 0'

. Can you know what is wrong?

Gordon Linoff Over a year ago

You would get NULL if @NumPI were NULL. You would get an error if NumPi were a number.

PJLG Over a year ago

Variables are set with values which should return a specific date. If I run the query changing variables with this values, I get that date...

Gordon Linoff Over a year ago

@PJLG . . . Silly me. This gets me like all the time. The output keyword has to be repeated twice, once in the parameter definition and once where it gets set. I miss this about 90% of the time when I use output parameters (and then typically remember right away).

PJLG Over a year ago

No problem, who does not happen similar situations? Either way, you fix the issue! It's perfect now. Thank you @Gordon!

Ajay2707 · Accepted Answer · 2014-05-24 13:51:03Z

I have done this type of issue.

Please check the sample code. Its working for me. Please change according.

DECLARE @SQLString NVARCHAR(500)
    DECLARE @ParmDefinition NVARCHAR(500)
    DECLARE @IntVariable INT    
    DECLARE @Lastlname varchar(30) = 'col10 '  -- this variable use for input in sp


    SET @SQLString = N'SELECT @LastlnameOUT = '+@Lastlname+'
                       FROM TempStoreExcelData '
    SET @ParmDefinition = N'@level tinyint,
                            @LastlnameOUT varchar(30) OUTPUT'
    SET @IntVariable = 35
    EXECUTE sp_executesql
        @SQLString,
        @ParmDefinition,
        @level = @IntVariable,
        @LastlnameOUT=@Lastlname OUTPUT
    SELECT @output = @Lastlname
select @output

Collectives™ on Stack Overflow

Define a SQL variable with Database Name

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related