如何在Int列中使用Varchar [英] How to Use Varchar with Int column
问题描述
我在MSSQL中写一个SP。其中一个参数是VARCHAR类型
,其中包含逗号分隔的INT值。我想在INT类型coloum的
WHERE子句中使用这个变量。我怎么做到这一点。
例如。 DECLARE @CompanyTypeIDs VARCHAR(200)
SET @CompanyTypeIDs =''1,3,2''
目前我用它作为
SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN(1,2,3)
CompanyTypeID属于INT类型。
但是我想要更换使用下面传递的参数进行了反对
SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN
(@CompanyTypeIDs) - 但这不会给出任何结果。
请让我知道解决方案。
非常感谢,
Mahesh
如果你想继续沿着这条路走下去,那么你需要构建整个SQL语句作为变量的
,并执行它,就像
以下:
DECLARE @CompanyTypeIDs VARCHAR(200)
SET @CompanyTypeIDs =''1,3,2' '
DECLARE @SQL nvarchar(4000)
SET @SQL =''SELECT Compa nyTypeID FROM公司WHERE CompanyTypeID IN(''
+ @CompanyTypeIDs +'')''
打印@SQL
EXEC(@SQL)
然而,这可能不是解决您问题的最佳方案,因为我是
确定其他人在这个小组会指出。
Stu
Stu,
谢谢很多你的promt回应。那么你建议以什么方式来支付
。实际上我将@CompanyTypeIDs作为参数来获得
SP。因此,请相应地建议。
我的PS非常大,以上是其中一个声明。我想要
将从上面的语句中收到的数据插入到#temp TABLE中。
谢谢,
Mahesh >
我建议的方法可以正常工作;所有我想说的是
其他人可能比我更好的建议。:)
Stu
Hi,
I am writing a SP in MSSQL. One of the parameted is VARCHAR type
which have comma seperated INT vlaues. I want to use this varible in
WHERE clause against INT type coloum. how do I achive this.
eg. DECLARE @CompanyTypeIDs VARCHAR(200)
SET @CompanyTypeIDs = ''1,3,2''
Currently I am using it as
SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN (1,2,3)
CompanyTypeID is of INT type.
But I want to replace the consts with the Parameter passed as below
SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN
(@CompanyTypeIDs) - but this is not giving any result.
Please let me know the solution.
Many Thanks,
Mahesh
If you want to continue along this path, then you need to construct
your entire SQL statement as a variable, and exec that, like the
following:
DECLARE @CompanyTypeIDs VARCHAR(200)
SET @CompanyTypeIDs = ''1,3,2''
DECLARE @SQL nvarchar (4000)
SET @SQL = ''SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN (''
+ @CompanyTypeIDs + '') ''
PRINT @SQL
EXEC(@SQL)
However, this might not be the best solution to your problem, as I''m
sure someone else on this group will point out.
Stu
Stu,
Thanks a lot for your promt response. Then what way do u suggest to
achive this. Actually I am getting @CompanyTypeIDs as aparameter to
SP. So, please suggest accordingly.
My PS is quite big and above is one of the statement. I am suppose to
insert the data recieived from above statement into #temp TABLE.
Thanks,
Mahesh
The method I suggested will work fine; all I meant to say was that
others might have a better suggestion than I. :)
Stu
这篇关于如何在Int列中使用Varchar的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!