我们如何在Store程序中的Dynemic查询中传递字符串值... [英] how we pass string value in Dynemic query in Store procedure...
问题描述
我想根据某些条件生成dynemic查询,我在我的C#页面上做了然后查询工作很好,现在我的条件是我想在Store程序中进行相同的查询,
但我的问题是如何将字符串值传递给此查询,
这是我的代码..
I want to generate dynemic query according to some condition,i made it on my C# page then query work well, now my condition is i want to make this same query in Store procedure,
but my problem is how are pass string value into this query,
this is my code..
ALTER PROCEDURE [dbo].[proc_JugmentSearchCount]
@Favour nvarchar(MAX)='Partly',
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DynamicSQL nvarchar(max)
SET @DynamicSQL = 'SELECT count(jid) FROM tblJudgements1 where scategoryname=''Judgements'''
Set @DynamicSQL = @DynamicSQL + ' And (favour =' +@Favour+')'
EXECUTE sp_executesql @DynamicSQL
END
查询不取@Favour字符串的值,如果我写的话像这样的查询然后它运作良好..
the query not take the value of "@Favour" string, and if i write query like this then it work well..
SET @DynamicSQL = 'SELECT count(jid) FROM tblJudgements1 where scategoryname=''Judgements'''
Set @DynamicSQL = @DynamicSQL + ' And (favour =''partly'')'
EXECUTE sp_executesql @DynamicSQL
但是在我的情况@Favour的价值来自我的C#页面......
所以我该怎么办... plz help .....
but in my case the value of @Favour is came at my C# page...
so what can i do... plz help.....
推荐答案
所有你需要调试的是添加行
All you needed to debug this was to add the line
SELECT @DynamicSQL
代码末尾。它会向你显示你错过了Partly周围的报价。
用第8行代替
at the end of your code. It would have shown you that you are missing the quotes around "Partly".
Replace line 8 with
Set @DynamicSQL = @DynamicSQL + ' And (favour =''' +@Favour+''')'
这很疯狂。你为什么要使用EXEC,绕过你从注入攻击中获得的保护,并假装你正在使用存储过程?为什么不这样做:
This is insane. Why would you use EXEC, which bypasses the protection you get from injection attacks, and pretend you're using stored procs ? Why not just do:
SELECT count(jid) FROM tblJudgements1 where scategoryname='Judgements' and (favour = @favour
另外,你最好在表格中存储类别和好处,然后通过id而不是字符串映射到它们。
Also, you'd do better to store categories and favours in tables, and then map to them via ids instead of strings.
这篇关于我们如何在Store程序中的Dynemic查询中传递字符串值...的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!