我们如何在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屋！