找到你要的答案

Q:How to Append SQL String in a Select Statement

Q:如何添加SQL SELECT语句中的字符串

I am trying to update the Where Clause of stored procedure based on if else

ALTER PROCEDURE spLocal_GetDetails
@Var1 varchar(255),
@Var2 varchar(255)
As
declare @WhereClause varchar(255)
IF @Var1 = '' and @Var2 = ''
SET @WhereClause='Where T1.Key1='+@Var1 
ELSE
SET @WhereClause= 'T1.Key1='+@Var1 + ' and someValue'+ @Var2+' 

Select *
from [dbo].[Table1] T1 
LEFT JOIN [dbo].[Table2] T2 with(nolock) on T1.KEY=T2.KEY
+ @WhereClause

Now I am getting the error:

Msg 245, Level 16, State 1, Procedure spLocal_GetDetails, Conversion failed when converting the varchar value 'Where T1.Key1=5' to data type int.

我试图更新存储过程的WHERE子句

ALTER PROCEDURE spLocal_GetDetails
@Var1 varchar(255),
@Var2 varchar(255)
As
declare @WhereClause varchar(255)
IF @Var1 = '' and @Var2 = ''
SET @WhereClause='Where T1.Key1='+@Var1 
ELSE
SET @WhereClause= 'T1.Key1='+@Var1 + ' and someValue'+ @Var2+' 

Select *
from [dbo].[Table1] T1 
LEFT JOIN [dbo].[Table2] T2 with(nolock) on T1.KEY=T2.KEY
+ @WhereClause

现在我得到的错误:

Msg 245, Level 16, State 1, Procedure spLocal_GetDetails, Conversion failed when converting the varchar value 'Where T1.Key1=5' to data type int.

answer1: 回答1:

I tend not to like generating dynamic SQL (here's why), in a reasonably simple case such as this I'd opt for something like the following...

ALTER PROCEDURE spLocal_GetDetails
     @Var1 varchar(255),
     @Var2 varchar(255)
As
    declare @BothKeysEmpty bit

    IF @Var1 = '' and @Var2 = ''
        SET @BothKeysEmpty = 1
    ELSE
        SET @BothKeysEmpty = 0

    select *
      from [dbo].[Table1] T1 
      left join [dbo].[Table2] T2 with(nolock) on T1.KEY=T2.KEY
     where T1.Key1= @Var1
       and (@BothKeysEmpty = 1 OR somevalue = @Var2)

我不喜欢生成动态SQL(这里的原因),在一个相当简单的这种情况下我会选择类似下面的…

ALTER PROCEDURE spLocal_GetDetails
     @Var1 varchar(255),
     @Var2 varchar(255)
As
    declare @BothKeysEmpty bit

    IF @Var1 = '' and @Var2 = ''
        SET @BothKeysEmpty = 1
    ELSE
        SET @BothKeysEmpty = 0

    select *
      from [dbo].[Table1] T1 
      left join [dbo].[Table2] T2 with(nolock) on T1.KEY=T2.KEY
     where T1.Key1= @Var1
       and (@BothKeysEmpty = 1 OR somevalue = @Var2)
answer2: 回答2:

There are three issues in your code.

First of all:

SET @WhereClause='Where T1.Key1='+@MyId

@Idseems to be an Integer. This means it should be converted!

SET @WhereClause='Where T1.Key1='+CONVERT(nvarchar(100),@MyId)

Second:

Where do you get @MyId from? I don't see it in your code.

And third:

SET @WhereClause= 'T1.Key1='+@MyId+ ' and someValue'+ @Var1+' and otherValue='+ @Var2

You try to put a string in the where clause. You forget the = and you need to mark them as strings using ''. See here:

SET @WhereClause= 'T1.Key1='+CONVERT(nvarchar(100),@MyId)+ ' and someValue='''+ @Var1+''' and otherValue='''+ @Var2 + ''''

你的代码有三个问题。

首先:

SET @WhereClause='Where T1.Key1='+@MyId

“Idseems是一个整数。这意味着它应该被转换!

SET @WhereClause='Where T1.Key1='+CONVERT(nvarchar(100),@MyId)

二:

你从哪里得到“不存在的?我没有看到它在您的代码。

和第三:

SET @WhereClause= 'T1.Key1='+@MyId+ ' and someValue'+ @Var1+' and otherValue='+ @Var2

你试着把字符串放在WHERE子句中。你忘记了=和你需要标记为字符串使用'。看到这里:

SET @WhereClause= 'T1.Key1='+CONVERT(nvarchar(100),@MyId)+ ' and someValue='''+ @Var1+''' and otherValue='''+ @Var2 + ''''
answer3: 回答3:

This is run in sqlserver

ALTER PROCEDURE spLocal_GetDetails
@Var1 varchar(255),
@Var2 varchar(255)
As
begin
declare @WhereClause varchar(255)
declare @query varchar(max)
IF @Var1 = '' and @Var2 = ''
SET @WhereClause='Where T1.Key1='+convert(varchar(50),@MyId)
ELSE
SET @WhereClause= 'where T1.Key1='+convert(varchar(50),@MyId)+ ' and someValue'+ @Var1+' and otherValue='+ @Var2

SET @query='Select *
from [dbo].[Table1] T1 
LEFT JOIN [dbo].[Table2] T2 with(nolock) on T1.KEY=T2.KEY '
+ @WhereClause

excute(@query)
end

这是运行SQLServer

ALTER PROCEDURE spLocal_GetDetails
@Var1 varchar(255),
@Var2 varchar(255)
As
begin
declare @WhereClause varchar(255)
declare @query varchar(max)
IF @Var1 = '' and @Var2 = ''
SET @WhereClause='Where T1.Key1='+convert(varchar(50),@MyId)
ELSE
SET @WhereClause= 'where T1.Key1='+convert(varchar(50),@MyId)+ ' and someValue'+ @Var1+' and otherValue='+ @Var2

SET @query='Select *
from [dbo].[Table1] T1 
LEFT JOIN [dbo].[Table2] T2 with(nolock) on T1.KEY=T2.KEY '
+ @WhereClause

excute(@query)
end
answer4: 回答4:

You are mixing sql and t-sql, you should build a query dynamically by creating a temp variable which will hold your sql and then execute it via t-sql

SET @query='
Select *
from [dbo].[Table1] T1 
LEFT JOIN [dbo].[Table2] T2 with(nolock) on T1.KEY=T2.KEY
'
+ @WhereClause

excute(@query)

你是混合的SQL和T-SQL,你应该建立一个动态的查询创建一个临时变量,将你的SQL,然后执行它通过T-SQL

SET @query='
Select *
from [dbo].[Table1] T1 
LEFT JOIN [dbo].[Table2] T2 with(nolock) on T1.KEY=T2.KEY
'
+ @WhereClause

excute(@query)
sql  sql-server  stored-procedures