T-SQL table name concatenate with string and @para

问题:

I want to get a count of my @sql before it execute. But I cannot concatenate table name with my parameter value.

SQL:

SET @count = (SELECT count(*) as tblcount 
              FROM RDL_tbl_UserManagement_UserInfo_ + @table_prefix_name 
              WHERE partnerID = CAST(@partnerID AS VARCHAR) 
                AND userID = CAST(@adminUserID AS VARCHAR) 
                AND addressType = 'H')

IF @count = 0
BEGIN
    SET @sql = 'SELECT * FROM RDL_tbl_UserManagement_UserInfo_' + @table_prefix_name +
               ' WHERE partnerID = ' + CAST(@partnerID AS VARCHAR) + 
               ' AND userID = ' + CAST(@adminUserID AS VARCHAR) + 
               ' AND addressType= ''MZ''  ORDER BY company'
END
ELSE
BEGIN           
    SET @sql = 'SELECT * FROM RDL_tbl_UserManagement_UserInfo_' + @table_prefix_name +
               ' WHERE partnerID = ' + CAST(@partnerID AS VARCHAR) +
               ' AND userID = ' + CAST(@adminUserID AS VARCHAR) + 
               ' AND addressType=''H''  ORDER BY company'
END

Exec (@sql)

How to do this ?

回答1:

 declare @count int
  SET @sql = 'SELECT @count =count(*) 
          FROM RDL_tbl_UserManagement_UserInfo_'+@table_prefix_name +'
          WHERE partnerID = CAST(@partnerID AS VARCHAR) 
            AND userID = CAST(@adminUserID AS VARCHAR) 
            AND addressType = ''H'')'

EXEC sp_executesql @sql, N'@count int OUTPUT',
                    @count OUTPUT

IF @count = 0
BEGIN
SET @sql = 'SELECT * FROM RDL_tbl_UserManagement_UserInfo_' + @table_prefix_name +
           ' WHERE partnerID = ' + CAST(@partnerID AS VARCHAR) + 
           ' AND userID = ' + CAST(@adminUserID AS VARCHAR) + 
           ' AND addressType= ''MZ''  ORDER BY company'
END
 ELSE
BEGIN           
SET @sql = 'SELECT * FROM RDL_tbl_UserManagement_UserInfo_' + @table_prefix_name +
           ' WHERE partnerID = ' + CAST(@partnerID AS VARCHAR) +
           ' AND userID = ' + CAST(@adminUserID AS VARCHAR) + 
           ' AND addressType=''H''  ORDER BY company'
 END

Exec (@sql)

回答2:

Just bring everything in your @sql parameter as

declare @sql navarchar(max)
set @sql = 
'declare @count int 
SET @count = (SELECT count(*) as tblcount FROM RDL_tbl_UserManagement_UserInfo_'+@table_prefix_name+' WHERE partnerID='+cast(@partnerID as varchar)+' AND userID='+cast(@adminUserID as varchar)+' AND addressType=''H'')
IF @count = 0
                BEGIN
                    SELECT * FROM RDL_tbl_UserManagement_UserInfo_'+@table_prefix_name+' WHERE partnerID='+cast(@partnerID as varchar)+' and userID='+cast(@adminUserID as varchar)+' AND addressType=''MZ''  ORDER BY company
                END
            ELSE
                BEGIN           
                    SELECT * FROM RDL_tbl_UserManagement_UserInfo_'+@table_prefix_name+' WHERE partnerID='+cast(@partnerID as varchar)+' and userID='+cast(@adminUserID as varchar)+' AND addressType=''H''  ORDER BY company
                END
'
Exec sp_executesql @sql