使用动态查询存储过程 [英] Store procedure with Dynamic query

问题描述

大家好，



我想在商店程序中进行动态查询...我有一个表将存储Table_Name和Field_Name名称为tblConfig



从tblConfig中选择*其中id = @id



将给出 Table_Name （比如tblUser）和 Field_Name （比如CID）



现在我想从Table_Name上面选择上面的Field_Name并输出结果，

类似在商店程序中选择（返回Field_Name）作为fld_data（返回Table_Name）。



所以，任何帮助或想法都将受到高度赞赏。



谢谢。

Hi every,

I would like to do dynamic querying in a store procedure... I have a table which will store Table_Name and Field_Name With name tblConfig

Select * from tblConfig where id = @id

will give Table_Name(like tblUser) and Field_Name(like CID)

Now i want to select above Field_Name from above Table_Name and out put the result,
something like select (returned Field_Name) as fld_data from (returned Table_Name) in a store procedure.

So, Any help or an idea about this will be highly appreciated.

Thanks.

推荐答案

试试关注SQL

Try following SQL

DECLARE @DynamicQuery AS NVARCHAR(MAX)
DECLARE @ColNames AS NVARCHAR(MAX)
DECLARE @TblName AS NVARCHAR(MAX)
SELECT @ColNames= ISNULL(@ColNames + ',','')+ QUOTENAME(Field_Name) FROM (Select Field_Name from tblConfig Where id =1/*@id*/) as fld_data
SELECT @TblName = Table_Name FROM (Select Table_Name from tblConfig Where id =1/*@id*/) as tbl_data
Print @ColNames
Print @TblName
SET @DynamicQuery =  N' Select ' + @ColNames + '   from ' + @TblName + ' ';
EXEC sp_executesql @DynamicQuery

declare @TableName nvarchar(50)
declare @FieldName nvarchar(50)
declare @Qry nvarchar(max)
set @TableName=(select Table_Name from tblConfig where id = @id)
set @FieldName=(select Field_Name from tblConfig where id = @id)

set @Qry=('select '+@FieldName ' from '+@TableName)
exec(@Qry)

你可以参考这个

you may refer to this

Create Proc GetValues(@ID as int)
As
BEGIN
    Declare @SQL Nvarchar(max),@table nvarchar(30),@fieldname nvarchar(30)

select @table=table,@field name=fieldname from tblconfig where id=@ID

    set @SQL='select '+@field name+' from '+@table 

    exec Sp_Execute @SQL
END

这篇关于使用动态查询存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！