如何将参数后期绑定到存储过程中的动态sql语句 [英] how to late parameter bind to dynamic sql statement in a stored procedure
问题描述
我正在尝试根据FieldMapping表和一些业务规则动态构建T-SQL语句.
I am attempting to dynamically build a T-SQL statement based on a FieldMapping table and some business rules.
总而言之,我有一个函数可以将SQL语句作为varchar(max)返回,并在存储过程中以EXEC (@Sql)
的身份执行.
Cut the long story short, I have a function that will return the SQL statement as a varchar(max) that I will execute as EXEC (@Sql)
in my stored procedure.
允许我通过测试表进行演示
Allow me to demonstrate with a test table
create procedure [dbo].[sp_TestInsert]
(
@Id int,
@Name varchar(20),
@Surname varchar(20),
@Age int,
@Source varchar(1)
)
as
declare @sql varchar(max)
-- Return SQL statement that depends on business rules
-- i.e. if the @Source = 'i' the returned SQL will be:
-- "update TestTable set Name = @Name, Surname = @Surname, SONbr = @SONbr WHERE Id = @Id"
-- however if the @Source = 'a' the returned SQL will be
-- "update TestTable set Name = @Name, Surname = @Surname, SONbr = @SONbr, Age = @Age WHERE Id = @Id"
-- As you can see, in case of 'i', it will NOT return Age = @Age
set @sql = dbo.func_UpdateOrInsert('TestTable', @Source)
-- When this statement is executed, the error I get is 'scalar @Name does not exist'
exec (@sql)
我已对该操作发表了评论.
I've commented on the operation.
问题很明显,我希望@ Id,@ Name,@ Surname等...在上下文中会自动绑定到相应的字段名称[Id],[Name],[Surname]等存储过程...但是,事实并非如此.
The problem is obvious, I would have expected that @Id, @Name, @Surname etc... would bind automatically to the corresponding field names [Id], [Name], [Surname] etc in the context of the stored procedure... however, this is not the case.
有什么方法可以将参数绑定到动态构建的SQL语句?
Is there any way I can bind the parameters to a dynamically built SQL statement?