Dynamic database name and store column value to variable

Question

I'm using SQL Server 2008. My stored procedure have to pass dynamic database name and store the column value into a variable

ALTER PROCEDURE [dbo].[proc_testproc](@mUserId int)
AS
declare @logCreate BIT;
declare @dbname sysname;
set @dbname = 'finaldb'

exec('SELECT ua.LogCreate AS ' + @logCreate + ' from '+ @dbname
   + 'dbo.User_Access as ua where ua.UserId=' + @mUserId + ')

IF @logCreate = 1 
--- Below some insertion happens based on the select query output--

Can anyone help me how to pass dynamic database name and store the value into a variable?

possible duplicate of [How to return values from a dynamic SQL Stored Procedure to the Entity Framework?](http://stackoverflow.com/questions/3166952/how-to-return-values-from-a-dynamic-sql-stored-procedure-to-the-entity-framework) — Taryn, Sep 17 '12 at 11:00

score 1 · Accepted Answer · answered Sep 17 '12 at 11:31

The ingredients you need are

Basically, build a dynamic SQL statement to be run using sp_executeSQL, and pass a variable into it. With the variable declared as an OUTPUT parameter, it can carry the result out of the dynamic SQL statement.

ALTER PROCEDURE [dbo].[proc_testproc](@mUserId int)
AS
declare @logCreate BIT;
declare @dbname sysname;
set @dbname = 'finaldb'

declare @sql nvarchar(max)
set @sql = '
    SELECT @logCreate=ua.LogCreate
    from '+ @dbname + '.dbo.User_Access as ua
    where ua.UserId=@mUserId'
exec sp_executesql @sql,
    N'@logCreate BIT output,@mUserId int',
    @logCreate output, @mUserId

IF @logCreate = 1

Dynamic database name and store column value to variable

1 Answers1