Converting delimited varchar @parameter for use in NOT IN()

I am creating a stored procedure which is passed a comma delimited string of
ids as a varchar datatype. The param is to be used in an SQL statement such
as:
CREATE PROCEDURE GetFromTable
@.IDs varchar(255)
AS
SELECT * FROM table WHERE iId NOT IN(@.IDs)
GO
The problem is that the iId field is of datatype int, so i get an error
converting the varchar datatype @.IDs to int.
I can not use dynamic SQL as i am not able to give table level access. It
has to be via EXEC rights on the stored procedure.
Any Help?
Thanks
PatrickArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
AMB
"Patrick Russell" wrote:

> I am creating a stored procedure which is passed a comma delimited string
of
> ids as a varchar datatype. The param is to be used in an SQL statement suc
h
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>
>|||You cannot do this "this way". You'd need to parse your string,
load the values into a TABLE variable and then reference your
table variable:
SELECT * FROM table WHERE iId NOT IN (select myid from @.MyTableVariable)
These two articles will help:
http://www.eggheadcafe.com/articles/20001002.asp
http://www.eggheadcafe.com/PrintSea...asp?LINKID=529
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Patrick,
Parse the @.IDs into rows of a temp table or table variable then use a join
or a subselect. The in operator will not take a variable like this without
building dynamic SQL.
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Hi Patrick.
You could write a function like...
-- pseudo code
create function udtSplitIDs( @.ids varchar(1000) )
returns @.IDsTable table
(
id int
)
as
begin
while (get position of comma)
begin
insert @.IDsTable values( @.strValue )
find next comma
end
return @.IDsTable
end
your select could then be:
SELECT * FROM table
WHERE iId NOT IN(SELECT * FROM udtSplitIDs(@.IDs))
Bryce|||Out of curiousity. A query like that should be avoided if possible in a
high-performance situation due to performance issues, I assume?

Converting a nvarchar parameter to datetime

i've got a stored procedure that gets a parameter as nvarchar, and
executes another procedure that needs the same parameter , but as
datetime
i've tried this: (where @.plantingDate is the parameter i get as
nvarchar)
CAST(@.PlantingDate as datetime)
and this:
CONVERT(datetime,@.PlantingDate)
but i get an error message....
what is the right way to do it'What values are you passing in for @.plantingDate? Can you give a couple of
examples of @.plantingDate values?
<friedman30@.gmail.com> wrote in message
news:1148223008.723472.278160@.j73g2000cwa.googlegroups.com...
> i've got a stored procedure that gets a parameter as nvarchar, and
> executes another procedure that needs the same parameter , but as
> datetime
> i've tried this: (where @.plantingDate is the parameter i get as
> nvarchar)
> CAST(@.PlantingDate as datetime)
> and this:
> CONVERT(datetime,@.PlantingDate)
> but i get an error message....
> what is the right way to do it'
>|||On 21 May 2006 07:50:08 -0700, friedman30@.gmail.com wrote:
>i've got a stored procedure that gets a parameter as nvarchar, and
>executes another procedure that needs the same parameter , but as
>datetime
>i've tried this: (where @.plantingDate is the parameter i get as
>nvarchar)
>CAST(@.PlantingDate as datetime)
>and this:
>CONVERT(datetime,@.PlantingDate)
>but i get an error message....
>what is the right way to do it'
Hi friedman30,
If you use CAST(@.PlantingDate AS datetime), the format @.PlantingDate has
to be one of the following:
* yyyymmdd (date only - no dashes, slashes, dots or other punctuation!!)
* yyyy-mm-ddThh:mm:ss (date and time - note the punctuation and the
uppercase "T" between date and time part)
* yyyy-mm-ddThh:mm:ss.mmm (date and time, including milliseconds).
With other formats (such as 03/04/05), it's up to the wisdom of SQL
Server te guess if this is inteded to be mm/ddd/yy, dd/mm/yy, or even
yy/mm/dd. Due to Murphy's Law, SQL Server will probably guess right in
development and wrong in production. <g>
If your nvarchar date is not in one of the above formats, then you can
use CONVERT with a style parameter to force SQL Server to use the chosen
style. E.g. CONVERT(datetime, '03/04/05', 11) will always be evaluated
to April 3rd, 2005. For a full list of style paramters and the
associated date and time formats, see CONVERT in Books Online.
--
Hugo Kornelis, SQL Server MVP|||THANKSsqlsql

Converting a nvarchar parameter to datetime

i've got a stored procedure that gets a parameter as nvarchar, and
executes another procedure that needs the same parameter , but as
datetime
i've tried this: (where @.plantingDate is the parameter i get as
nvarchar)
CAST(@.PlantingDate as datetime)
and this:
CONVERT(datetime,@.PlantingDate)
but i get an error message....
what is the right way to do it'What values are you passing in for @.plantingDate? Can you give a couple of
examples of @.plantingDate values?
<friedman30@.gmail.com> wrote in message
news:1148223008.723472.278160@.j73g2000cwa.googlegroups.com...
> i've got a stored procedure that gets a parameter as nvarchar, and
> executes another procedure that needs the same parameter , but as
> datetime
> i've tried this: (where @.plantingDate is the parameter i get as
> nvarchar)
> CAST(@.PlantingDate as datetime)
> and this:
> CONVERT(datetime,@.PlantingDate)
> but i get an error message....
> what is the right way to do it'
>|||On 21 May 2006 07:50:08 -0700, friedman30@.gmail.com wrote:

>i've got a stored procedure that gets a parameter as nvarchar, and
>executes another procedure that needs the same parameter , but as
>datetime
>i've tried this: (where @.plantingDate is the parameter i get as
>nvarchar)
>CAST(@.PlantingDate as datetime)
>and this:
>CONVERT(datetime,@.PlantingDate)
>but i get an error message....
>what is the right way to do it'
Hi friedman30,
If you use CAST(@.PlantingDate AS datetime), the format @.PlantingDate has
to be one of the following:
* yyyymmdd (date only - no dashes, slashes, dots or other punctuation!!)
* yyyy-mm-ddThh:mm:ss (date and time - note the punctuation and the
uppercase "T" between date and time part)
* yyyy-mm-ddThh:mm:ss.mmm (date and time, including milliseconds).
With other formats (such as 03/04/05), it's up to the wisdom of SQL
Server te guess if this is inteded to be mm/ddd/yy, dd/mm/yy, or even
yy/mm/dd. Due to Murphy's Law, SQL Server will probably guess right in
development and wrong in production. <g>
If your nvarchar date is not in one of the above formats, then you can
use CONVERT with a style parameter to force SQL Server to use the chosen
style. E.g. CONVERT(datetime, '03/04/05', 11) will always be evaluated
to April 3rd, 2005. For a full list of style paramters and the
associated date and time formats, see CONVERT in Books Online.
Hugo Kornelis, SQL Server MVP|||THANKS

convert input parameter into field

create procedure [dbo].[findtext]
(
@.fieldname nvarchar(50),
@.searchtext nvarchar(50)
)
AS
SELECT * FROM tablename WHERE @.fieldname = @.searchtext
it doesn't work!Hi Joe,
Use dynamic SQL to build the SQL statement, something like this:
DECLARE @.sql nvarchar(200)
Set @.sql = 'SELECT * FROM tablename WHERE ' + @.fieldname + ' = ''' +
@.searchtext + ''''@.searchtext '''
EXEC(@.sql)
Ray
"joe" wrote:

> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!|||joe
You will have to use dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
"joe" <joe@.discussions.microsoft.com> wrote in message
news:97C05AE4-5103-4133-816F-5AD53715DC04@.microsoft.com...
> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!|||Like rb and Uri said, dynamic SQL. Be careful to validate your data though,
since that could open you up to SQL injection attacks.
"joe" <joe@.discussions.microsoft.com> wrote in message
news:97C05AE4-5103-4133-816F-5AD53715DC04@.microsoft.com...
> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!

Convert DateTime Parameter to String

I have 2 reports and i have enabled Drilldown(Navigation) for the 1st Report.
One of the Parameter is DateTime which is passed to the 2 reports which should
treat this parameter as string since it's defined as string. How will i
convert the
DateTime to string Parameter for the Drilldown report. I tried all these
scenarios
for Parameter expressions and it doesn't work.
Convert.ToString(Parameters!Parameter2.value)
Convert.ToDateTime(Parameters!Parameter2.value).ToShortDateString()
FormatDateTime(Parameters!Parameter2.value,"mm/dd/yyyy")
Any help in this regard is appreciated? . I don't change the parameter for
second report to DateTime. Since it calls a WebService with parameters which
expects a string and not a System.DateTime.Try:
=Format(Parameters!Parameter2.value,"MM/dd/yyyy")
Note month is MM=month not mm=minutes.
HTH,
Magendo_man
"RAGHAVAN JAYARAMAN" wrote:
> I have 2 reports and i have enabled Drilldown(Navigation) for the 1st Report.
> One of the Parameter is DateTime which is passed to the 2 reports which should
> treat this parameter as string since it's defined as string. How will i
> convert the
> DateTime to string Parameter for the Drilldown report. I tried all these
> scenarios
> for Parameter expressions and it doesn't work.
> Convert.ToString(Parameters!Parameter2.value)
> Convert.ToDateTime(Parameters!Parameter2.value).ToShortDateString()
> FormatDateTime(Parameters!Parameter2.value,"mm/dd/yyyy")
> Any help in this regard is appreciated? . I don't change the parameter for
> second report to DateTime. Since it calls a WebService with parameters which
> expects a string and not a System.DateTime.
>|||Thanks for the reply. I tried it,it didn't work but somehow i got it working
with Cdate syntax
= CDate(Parameters!Parameter2.value).ToShortDateString();
"magendo_man" wrote:
> Try:
> =Format(Parameters!Parameter2.value,"MM/dd/yyyy")
> Note month is MM=month not mm=minutes.
> HTH,
> Magendo_man
>
> "RAGHAVAN JAYARAMAN" wrote:
> > I have 2 reports and i have enabled Drilldown(Navigation) for the 1st Report.
> > One of the Parameter is DateTime which is passed to the 2 reports which should
> > treat this parameter as string since it's defined as string. How will i
> > convert the
> > DateTime to string Parameter for the Drilldown report. I tried all these
> > scenarios
> > for Parameter expressions and it doesn't work.
> >
> > Convert.ToString(Parameters!Parameter2.value)
> > Convert.ToDateTime(Parameters!Parameter2.value).ToShortDateString()
> > FormatDateTime(Parameters!Parameter2.value,"mm/dd/yyyy")
> >
> > Any help in this regard is appreciated? . I don't change the parameter for
> > second report to DateTime. Since it calls a WebService with parameters which
> > expects a string and not a System.DateTime.
> >