SQL Server : dynamic query

Question

I have a SQL Server 2008 with a table that acts like a hash-map. basically, there's three columns (id, key, val) and I need to pull the other columns a, b, c, d, e.

The purpose is to basically choose the database I need to query data from. Similar to this issue Using the correct database

I have got the SQL using a brute force method. I'm just trying do this in a better, more efficient way

Here's the SQL I'm trying to get to work:

CREATE PROCEDURE [dbo].[me] 
    @partitionName VARCHAR(64),
    @id INT
AS
BEGIN
    SET NOCOUNT ON
    SET ROWCOUNT 0

    DECLARE @table as varchar(128)
    DECLARE @sql as nvarchar(4000)
    DECLARE @params as nvarchar(4000)
    DECLARE @s_key as varchar(64)
    DECLARE @paramDefinition as nvarchar(4000)

   DECLARE @a INT
   DECLARE @b VARCHAR(32)
   DECLARE @c VARCHAR(32)
   DECLARE @d VARCHAR(32)
   DECLARE @e VARCHAR(32)

   SET @table = @partitionName + '.dbo.hash_table'

   SET @sql = 
      N'SELECT ' +
      N'    @a = MAX(CASE WHEN [key] = ''a'' THEN value ELSE '''' END),
            @b = MAX(CASE WHEN [key] = ''b'' THEN value ELSE '''' END),
            @c = MAX(CASE WHEN [key] = ''c'' THEN value ELSE '''' END),
            @d = MAX(CASE WHEN [key] = ''d'' THEN value ELSE '''' END),
            @e = MAX(CASE WHEN [key] = ''e'' THEN value ELSE '''' END)
    FROM ' + @table + 
    N'WHERE id = ' + CONVERT(VARCHAR(3), @id)

    EXEC sp_executesql @sql

But, this gives the following error

Must declare the scalar variable "@a"

I have a feeling I need to do something like pass in @paramsDefinition to sp_executesql

But so far, these have failed

SET @paramDefinition = '@a INT OUTPUT, '
+ ' @b varchar(32) OUTPUT, '
+ ' @c varchar(32) OUTPUT, '
+ ' @d varchar(32) OUTPUT,' 
+ ' @r varchar(32) OUTPUT'
...
EXEC sp_executesql @sql, @paramDefintions

I get

Incorrect syntax near '='.

Here is the brute force method (which works but hits the DB 5 times)

SET @key = 'a'
SET @sql = 
N' SELECT @a = val FROM ' + @table +
N' WHERE key = ' + quotename(@key, '''') +
N' AND id = ' + CONVERT(VARCHAR(3), @nid)
EXEC sp_executesql @sql, N'@a varchar(32) OUTPUT', @a = @a OUTPUT

SET @key = 'b'
SET @sql = 
N' SELECT @b = val FROM ' + @table +
N' WHERE key = ' + quotename(@key, '''') +
N' AND id = ' + CONVERT(VARCHAR(3), @id)
EXEC sp_executesql @sql, N'@b varchar(32) OUTPUT', @b = @b OUTPUT

SET @key = 'c'
SET @sql = 
N' SELECT @c = val FROM ' + @table +
N' WHERE key = ' + quotename(@key, '''') +
N' AND id = ' + CONVERT(VARCHAR(3), @id)
EXEC sp_executesql @sql, N'@c varchar(32) OUTPUT', @c = @c OUTPUT

SET @key = 'd'
SET @sql = 
N' SELECT @d = val FROM ' + @table +
N' WHERE key = ' + quotename(@key, '''') +
N' AND id = ' + CONVERT(VARCHAR(3), @id)
EXEC sp_executesql @sql, N'@d varchar(32) OUTPUT', @d = @d OUTPUT

SET @key = 'e'
SET @sql = 
N' SELECT @e = val FROM ' + @table +
N' WHERE key = ' + quotename(@key, '''') +
N' AND id = ' + CONVERT(VARCHAR(3), @id)
EXEC sp_executesql @sql, N'@e varchar(32) OUTPUT', @e = @e OUTPUT

SELECT @a as [a], @b as [b], @c as [c], @d as [d], @r as [r]

Answer 1

Try this.....

Dynamic SQL has its own scope, Variables in your stored procedure arent visible to to your dynamic sql, you will need to declare these variables as second parameter to sp_execuetsql and since you are reverting values back in these variables, you will need to use key word OUTPUT with these variables when passing them to sp_executesql , as follows

CREATE PROCEDURE [dbo].[me] 
   @partitionName SYSNAME,
   @id            INT
AS
BEGIN
  SET NOCOUNT ON;

    DECLARE @table as varchar(128)
    DECLARE @sql as nvarchar(4000)
    DECLARE @params as nvarchar(4000)
    DECLARE @s_key as varchar(64)
    DECLARE @paramDefinition as nvarchar(4000)

    DECLARE @a INT
    DECLARE @b VARCHAR(32)
    DECLARE @c VARCHAR(32)
    DECLARE @d VARCHAR(32)
    DECLARE @e VARCHAR(32)

 SET @table =  @partitionName + 'hash_table'

SET @sql =  N'SELECT ' +
          N'    @a = MAX(CASE WHEN [key] = ''a'' THEN value ELSE '''' END),
                @b = MAX(CASE WHEN [key] = ''b'' THEN value ELSE '''' END),
                @c = MAX(CASE WHEN [key] = ''c'' THEN value ELSE '''' END),
                @d = MAX(CASE WHEN [key] = ''d'' THEN value ELSE '''' END),
                @e = MAX(CASE WHEN [key] = ''e'' THEN value ELSE '''' END)
                FROM dbo.' + QUOTENAME(@table) + 
                N' WHERE id = @id)'

EXEC sp_executesql @sql
                  ,N'@a INT OUTPUT, @b VARCHAR(32) OUTPUT,@b VARCHAR(32) OUTPUT,@c VARCHAR(32) OUTPUT, 
                      @d VARCHAR(32) OUTPUT,@e VARCHAR(32) OUTPUT, @id INT'
                  ,@a OUTPUT
                  ,@b OUTPUT
                  ,@c OUTPUT
                  ,@d OUTPUT
                  ,@e OUTPUT
                  ,@id
END

Answer 2

You can try to use a temporary table like this:

CREATE PROCEDURE [dbo].[me] 

@partitionName VARCHAR(64),
@id INT
AS
BEGIN
SET NOCOUNT ON
SET ROWCOUNT 0

DECLARE @table as varchar(128)
DECLARE @sql as nvarchar(4000)
DECLARE @params as nvarchar(4000)
DECLARE @s_key as varchar(64)
DECLARE @paramDefinition as nvarchar(4000)

  DECLARE @a INT
  DECLARE @b VARCHAR(32)
  DECLARE @c VARCHAR(32)
  DECLARE @d VARCHAR(32)
  DECLARE @e VARCHAR(32)

  SET @table =  @partitionName + '.dbo.hash_table'

  CREATE TABLE #tmp (code varchar(50))

  SET @sql = 
  N'SELECT ' +
  N'    MAX(CASE WHEN [key] = ''a'' THEN value ELSE '''' END) as [a],
        MAX(CASE WHEN [key] = ''b'' THEN value ELSE '''' END) as [b],
        MAX(CASE WHEN [key] = ''c'' THEN value ELSE '''' END) as [c],
        MAX(CASE WHEN [key] = ''d'' THEN value ELSE '''' END) as [d],
        MAX(CASE WHEN [key] = ''e'' THEN value ELSE '''' END) as [e]
FROM ' + @table + 
N'WHERE id = ' + CONVERT(VARCHAR(3), @id)

INSERT INTO  #tmp (code)
EXEC sp_executesql @sql

SELECT * from #tmp

Hope it helps.