Failed to convert parameter value from a String to a Guid?

Hi all,

I have setup my database that have 3 columns:

1. Primary Key

2. UserId (UniqueIdentifier, Guid I guess) - I set it up so I can insert the value from the Membership table (UserId) for some relationship.

3. Another Foreign key with just an int.

I tried to build a DAL, ran a test and received this error: "Failed to convert parameter value from a String to a Guid." Before I setup my UserId to be UniqueIdentifier and let it be just an Int, I don't have any problem retrieving data. Here is the SELECT query that I built with the DataSet:

SELECT aspnet_Users.UserId, t_music.MUSIC_TITLE
FROM t_user_viewed
JOIN aspnet_Users ON aspnet_Users.UserId = t_user_viewed.UserId
JOIN t_music ON t_music.MUSIC_PK = t_user_viewed.MUSIC_PK_FK
WHERE aspnet_Users.UserId = @.UserId

Any help would be greatly appreciated,

Kenny.

You need to post definition for @.UserId parameter.

|||

Can you please elaborates? I'm new to asp.net as well as ms-sql.

Thank you,

Kenny.

|||

Sounds like you need to cast the string value to GUID on the @.UserId when you call the select function and pass the @.UserId value.

ctype(StringValueGUID, GUID)

Burl

|||

Where do I cast the string value? In the DAL (dataset) or where I have my ObjectDataSource doing the select? Sorry for my lack of knowledge on asp.net and mssql.

Thanks,

Kenny.

|||

How are you passing the GUID to the ObjectDataSource for the select? Wherever that value comes from, its in a string type and needs to be cast as a GUID type. Can you post some code?

Burl

|||

I've created a Dataset and setup my table, then I've created a BLL that have this code to get the fields:

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
public NotesNhac.t_user_viewedDataTable GetSongsByUserPK(Guid UserId)
{
return Adapter2.GetSongsByUserPK(UserId);
}

In my ObjectDataSource, I chose the above method (GetSongsByUserPK) and have the parameter source as a session. Here is the code for the ObjectDataSource:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSongsByUserPK"
TypeName="SongsBBL">
<SelectParameters>
<asp:SessionParameter Name="UserId" SessionField="UserId" Type="Object" />
</SelectParameters>
</asp:ObjectDataSource>

The SELECT query is the same as my first post... is there anything else I need to do?

Thank you very, very much.

Kenny.

|||

Well,

I've made some minor adjustment to my code and it's working now, however, the SELECT statement that I wrote in the DataSet didn't return the proper table. Here are the changes:

1. In my DataSet, I have setup the table called t_user_viewed that will store the primary key of t_music database as foreign key, and the UserId from the aspet_users as another foreign key. Here is the select statement:

SELECT *
FROM t_music a
JOIN t_user_viewed b on a.MUSIC_PK = b.MUSIC_PK_FK
JOIN aspnet_Users c ON b.UserID = c.UserId
WHERE b.UserID = @.UserId

So when I run this code, and enter the UserId, it should return the information from table "t_music" (with all information such as song title, artist, etc...) instead.

2. This is the code for the SongsBLL.cs class, to get the above select statement:

private t_user_viewedTableAdapter _t_user_viewed = null;

protected t_user_viewedTableAdapter Adapter2
{
get
{
if (_t_user_viewed == null)
_t_user_viewed = new t_user_viewedTableAdapter();

return _t_user_viewed;
}
}

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
public NotesNhac.t_user_viewedDataTable GetSongsByUserPK(Guid UserId)
{
return Adapter2.GetSongsByUserPK(UserId);
}

3. Here is the ObjectDataSource that was generated by VS:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSongsByUserPK"
TypeName="SongsBBL" OldValuesParameterFormatString="original_{0}">
<SelectParameters>
<asp:SessionParameter Name="UserId" SessionField="UserId" Type="Object" />
</SelectParameters>
</asp:ObjectDataSource>

4. Here is the GridView that was generated automatically when I select the DataSourceID to the above ObjectDataSource:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="VIEWED_PK"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:BoundField DataField="VIEWED_PK" HeaderText="VIEWED_PK" InsertVisible="False"
ReadOnly="True" SortExpression="VIEWED_PK" />
<asp:BoundField DataField="MUSIC_PK_FK" HeaderText="MUSIC_PK_FK" SortExpression="MUSIC_PK_FK" />
<asp:BoundField DataField="UserID" HeaderText="UserID" SortExpression="UserID" />
</Columns>
</asp:GridView>

Apparently, the GridView & the ObjectDataSource only pick up the t_music_viewed table columns, instead of the "t_music" columns that I specified in my Select statement.

Any idea?

Thank you very much,

Kenny.

|||

I've solved it by building my SELECT query in the "t_music" table instead.

THANK YOU!

Kenny.

|||

I've solved it by building my SELECT query in the "t_music" table instead.

THANK YOU!

Kenny.

sql

facing problem in increasing connection string pool size sqlserver

Hi to all,

I am using a connection string like

data source=RemoteHostName;initial catalog=myDb;password=sa;user id=sa;
Max pool size = 200;

And now strange thing is happening ,, I am receiving error :

Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached

The SqlServer Activity Manager is telling that only 100 connections are pooled, and I guess that the Max pool size is 100, It is not being changed by my Connection string. As I am trying to change the default 100 pool size value to 200.

Huh , So stucked up , how to increase the Max pool size.. Is there any way .

I am getting worrried.

Any help ??

Thx and RegardsBill Vaughn is a well-regarded SQL Server expert. This article of his should help you out:The .NET Connection Pool Lifeguard -- Prevent pool overflows that can drown your applications.

Terri

Extracting Month Name from Date

Is there a function for getting the Month name from a date string? For
example, '01/20/2004' would return 'January'I think it's MonthName()
MonthName(getdate()) would return October
"Blake Gremillion" <Blake Gremillion@.discussions.microsoft.com> wrote in
message news:C3E99BBD-C664-4E50-B849-D19B72B12B97@.microsoft.com...
> Is there a function for getting the Month name from a date string? For
> example, '01/20/2004' would return 'January'
>|||DATENAME(MONTH, GATDATE())
"Kelly" <kelly.hauser@.amerock.com> wrote in message
news:ectKTi#qEHA.2764@.TK2MSFTNGP11.phx.gbl...
> I think it's MonthName()
> MonthName(getdate()) would return October
>
> "Blake Gremillion" <Blake Gremillion@.discussions.microsoft.com> wrote in
> message news:C3E99BBD-C664-4E50-B849-D19B72B12B97@.microsoft.com...
> > Is there a function for getting the Month name from a date string? For
> > example, '01/20/2004' would return 'January'
> >
> >
>

extracting from a string value

I have something like this

E:\path1\subpath\Summary Report.xml in a database column

And what I want to pull out of the string is

Summary Report

How can I pull this out with the only thing that is constant is that the string is always between the last back slash and the .XML extension.

Code Snippet

string text = objRow("Path").ToString();
string fileName = text.Substring(text.LastIndexOf("\") + 1);
fileName = fileName.Replace(".xml", string.Empty); //this could also be a substring operation if the extension changes

|||I need to use T-SQL to do it|||

Another way that is a bit over-the-top is something like:

Code Snippet

select reverse(replace(reverse(rtrim(right('E:\path1\subpath\Summary Report.xml ', charindex('\', reverse('E:\path1\subpath\Summary Report.xml '))-1))), 'lmx.', '')) as theFile

/*
theFile
--
Summary Report
*/

|||

something like this works:

Code Snippet

declare @.path varchar(50)

set @.path ='E:\path1\subpath\Summary Report.xml'

selectsubstring(@.path,len(@.path)-charindex('\',reverse(@.path))+2,

charindex('\',reverse(@.path))-5)

|||

Try:

declare @.s varchar(50)

set @.s ='E:\path1\subpath\Summary Report.xml'

selectsubstring(@.s,len(@.s)-patindex('%\%',reverse(@.s))+ 2,patindex('%\%',reverse(@.s))- 5)

AMB

extracting from a string

I am trying to extract data within the first set of parenthesis in a string
that often times have multiple sets of data ..for example
(22)(223)(45) I just want the (22) or
(234)(12)...here just need the (234)
I see lots of text help on lengths but none that address a specified
character beginning and ending...
Thanks for any assistanceTry:
declare @.str varchar(100)
set @.str = '(22)(223)(45)'
select
substring (@.str, charindex ('(', @.str), charindex (')', @.str))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
I am trying to extract data within the first set of parenthesis in a string
that often times have multiple sets of data ..for example
(22)(223)(45) I just want the (22) or
(234)(12)...here just need the (234)
I see lots of text help on lengths but none that address a specified
character beginning and ending...
Thanks for any assistance|||Tom, here is my statement with you syntax...all I am getting is (22)
declare @.str varchar(100)
set @.str = '(22)(223)(45)'
select dbo.products_ourproducts_products.folderlist,
substring (@.str, charindex ('(', @.str), charindex (')', @.str))
from dbo.products_ourproducts_products
"Tom Moreau" wrote:
> Try:
> declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select
> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> I am trying to extract data within the first set of parenthesis in a string
> that often times have multiple sets of data ..for example
> (22)(223)(45) I just want the (22) or
> (234)(12)...here just need the (234)
> I see lots of text help on lengths but none that address a specified
> character beginning and ending...
> Thanks for any assistance
>
>|||Tom provided an example of the operation using a @.string variable. The
idea was that you would apply that expression to YOUR data. So, if
dbo.products_ourproducts_products.folderlist is the column from which
you want to extract the first item, it would be:
SELECT substring (X.folderlist,
charindex ('(', X.folderlist),
charindex (')', X.folderlist))
FROM dbo.products_ourproducts_products as X
All I did was replace @.str with your column reference. (And assign an
alias to make things more readable.)
Roy Harvey
Beacon Falls, CT
On Wed, 30 Apr 2008 13:59:05 -0700, Gerry M
<GerryM@.discussions.microsoft.com> wrote:
>Tom, here is my statement with you syntax...all I am getting is (22)
>declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select dbo.products_ourproducts_products.folderlist,
> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> from dbo.products_ourproducts_products
>
>"Tom Moreau" wrote:
>> Try:
>> declare @.str varchar(100)
>> set @.str = '(22)(223)(45)'
>> select
>> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
>> I am trying to extract data within the first set of parenthesis in a string
>> that often times have multiple sets of data ..for example
>> (22)(223)(45) I just want the (22) or
>> (234)(12)...here just need the (234)
>> I see lots of text help on lengths but none that address a specified
>> character beginning and ending...
>> Thanks for any assistance
>>|||Tom, I might not be explaing very well, I have thousands of records and in
this field (folderlist)I have many different values...I only want the first
value in the field enclosed in parenthesis...some examples of the data are as
follows
(22)(223)(45) I want the (22)
(272)(24423) I want the (222)
(6) I want the (6)
(3422)(223)(45) I want the (3422)
Thanks
"Tom Moreau" wrote:
> Try:
> declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select
> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> I am trying to extract data within the first set of parenthesis in a string
> that often times have multiple sets of data ..for example
> (22)(223)(45) I just want the (22) or
> (234)(12)...here just need the (234)
> I see lots of text help on lengths but none that address a specified
> character beginning and ending...
> Thanks for any assistance
>
>|||Thank you, that worked fine.
"Roy Harvey (SQL Server MVP)" wrote:
> Tom provided an example of the operation using a @.string variable. The
> idea was that you would apply that expression to YOUR data. So, if
> dbo.products_ourproducts_products.folderlist is the column from which
> you want to extract the first item, it would be:
> SELECT substring (X.folderlist,
> charindex ('(', X.folderlist),
> charindex (')', X.folderlist))
> FROM dbo.products_ourproducts_products as X
> All I did was replace @.str with your column reference. (And assign an
> alias to make things more readable.)
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Apr 2008 13:59:05 -0700, Gerry M
> <GerryM@.discussions.microsoft.com> wrote:
> >Tom, here is my statement with you syntax...all I am getting is (22)
> >
> >declare @.str varchar(100)
> > set @.str = '(22)(223)(45)'
> >
> > select dbo.products_ourproducts_products.folderlist,
> > substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> >
> > from dbo.products_ourproducts_products
> >
> >
> >"Tom Moreau" wrote:
> >
> >> Try:
> >>
> >> declare @.str varchar(100)
> >> set @.str = '(22)(223)(45)'
> >>
> >> select
> >> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> >>
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> >> SQL Server MVP
> >> Toronto, ON Canada
> >> https://mvp.support.microsoft.com/profile/Tom.Moreau
> >>
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> >> I am trying to extract data within the first set of parenthesis in a string
> >> that often times have multiple sets of data ..for example
> >>
> >> (22)(223)(45) I just want the (22) or
> >> (234)(12)...here just need the (234)
> >>
> >> I see lots of text help on lengths but none that address a specified
> >> character beginning and ending...
> >>
> >> Thanks for any assistance
> >>
> >>
> >>
>|||I have the value I need from the string, can I now remove the just
parenthesis that enclose it?
"Gerry M" wrote:
> Thank you, that worked fine.
>
> "Roy Harvey (SQL Server MVP)" wrote:
> > Tom provided an example of the operation using a @.string variable. The
> > idea was that you would apply that expression to YOUR data. So, if
> > dbo.products_ourproducts_products.folderlist is the column from which
> > you want to extract the first item, it would be:
> >
> > SELECT substring (X.folderlist,
> > charindex ('(', X.folderlist),
> > charindex (')', X.folderlist))
> > FROM dbo.products_ourproducts_products as X
> >
> > All I did was replace @.str with your column reference. (And assign an
> > alias to make things more readable.)
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Wed, 30 Apr 2008 13:59:05 -0700, Gerry M
> > <GerryM@.discussions.microsoft.com> wrote:
> >
> > >Tom, here is my statement with you syntax...all I am getting is (22)
> > >
> > >declare @.str varchar(100)
> > > set @.str = '(22)(223)(45)'
> > >
> > > select dbo.products_ourproducts_products.folderlist,
> > > substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> > >
> > > from dbo.products_ourproducts_products
> > >
> > >
> > >"Tom Moreau" wrote:
> > >
> > >> Try:
> > >>
> > >> declare @.str varchar(100)
> > >> set @.str = '(22)(223)(45)'
> > >>
> > >> select
> > >> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> > >>
> > >>
> > >> --
> > >> Tom
> > >>
> > >> ----
> > >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > >> SQL Server MVP
> > >> Toronto, ON Canada
> > >> https://mvp.support.microsoft.com/profile/Tom.Moreau
> > >>
> > >>
> > >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> > >> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> > >> I am trying to extract data within the first set of parenthesis in a string
> > >> that often times have multiple sets of data ..for example
> > >>
> > >> (22)(223)(45) I just want the (22) or
> > >> (234)(12)...here just need the (234)
> > >>
> > >> I see lots of text help on lengths but none that address a specified
> > >> character beginning and ending...
> > >>
> > >> Thanks for any assistance
> > >>
> > >>
> > >>
> >|||On Thu, 1 May 2008 06:11:01 -0700, Gerry M
<GerryM@.discussions.microsoft.com> wrote:
>I have the value I need from the string, can I now remove the just
>parenthesis that enclose it?
Here is a version of what Tom originally posted, modified to leave off
the parentheses.
declare @.str varchar(100)
set @.str = '(22)(223)(45)'
select
substring(@.str, 2, charindex (')', @.str) -2)
I trust that you can apply this new expression in place of the old
version.
Roy Harvey
Beacon Falls, CT|||Roy, thanks for your help...I am getting a "Invalid length parameter passed
to the substring function." error with the following syntax..
update dbo.products_ourproducts_products set extratext02=substring(folderlist,2,charindex (')', folderlist) -2)
FROM dbo.products_ourproducts_products
Thanks again...
"Roy Harvey (SQL Server MVP)" wrote:
> On Thu, 1 May 2008 06:11:01 -0700, Gerry M
> <GerryM@.discussions.microsoft.com> wrote:
> >I have the value I need from the string, can I now remove the just
> >parenthesis that enclose it?
> Here is a version of what Tom originally posted, modified to leave off
> the parentheses.
> declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select
> substring(@.str, 2, charindex (')', @.str) -2)
> I trust that you can apply this new expression in place of the old
> version.
> Roy Harvey
> Beacon Falls, CT
>|||What do you get when you run the following:
SELECT charindex (')', folderlist),
folderlist
FROM dbo.products_ourproducts_products
WHERE charindex (')', folderlist) < 3
Roy Harvey
Beacon Falls, CT
On Thu, 1 May 2008 10:31:00 -0700, Gerry M
<GerryM@.discussions.microsoft.com> wrote:
>Roy, thanks for your help...I am getting a "Invalid length parameter passed
>to the substring function." error with the following syntax..
>update dbo.products_ourproducts_products set extratext02=>substring(folderlist,2,charindex (')', folderlist) -2)
>FROM dbo.products_ourproducts_products
>Thanks again...
>"Roy Harvey (SQL Server MVP)" wrote:
>> On Thu, 1 May 2008 06:11:01 -0700, Gerry M
>> <GerryM@.discussions.microsoft.com> wrote:
>> >I have the value I need from the string, can I now remove the just
>> >parenthesis that enclose it?
>> Here is a version of what Tom originally posted, modified to leave off
>> the parentheses.
>> declare @.str varchar(100)
>> set @.str = '(22)(223)(45)'
>> select
>> substring(@.str, 2, charindex (')', @.str) -2)
>> I trust that you can apply this new expression in place of the old
>> version.
>> Roy Harvey
>> Beacon Falls, CT|||I get the following 7 records (of a selection of 2100)
(No column name) folderlist
0
0
0
0
0
0
0
"Roy Harvey (SQL Server MVP)" wrote:
> What do you get when you run the following:
> SELECT charindex (')', folderlist),
> folderlist
> FROM dbo.products_ourproducts_products
> WHERE charindex (')', folderlist) < 3
> Roy Harvey
> Beacon Falls, CT
> On Thu, 1 May 2008 10:31:00 -0700, Gerry M
> <GerryM@.discussions.microsoft.com> wrote:
> >Roy, thanks for your help...I am getting a "Invalid length parameter passed
> >to the substring function." error with the following syntax..
> >
> >update dbo.products_ourproducts_products set extratext02=> >substring(folderlist,2,charindex (')', folderlist) -2)
> >FROM dbo.products_ourproducts_products
> >
> >Thanks again...
> >
> >"Roy Harvey (SQL Server MVP)" wrote:
> >
> >> On Thu, 1 May 2008 06:11:01 -0700, Gerry M
> >> <GerryM@.discussions.microsoft.com> wrote:
> >>
> >> >I have the value I need from the string, can I now remove the just
> >> >parenthesis that enclose it?
> >>
> >> Here is a version of what Tom originally posted, modified to leave off
> >> the parentheses.
> >>
> >> declare @.str varchar(100)
> >> set @.str = '(22)(223)(45)'
> >>
> >> select
> >> substring(@.str, 2, charindex (')', @.str) -2)
> >>
> >> I trust that you can apply this new expression in place of the old
> >> version.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
>

Extracting data from a variable

hi All,

I have a string varaible passed to the SP something like :

@.var = 'v1#1@.v2#1,2@.v3#1,3,4,5'. Now i have to extract the data from this variable in such a way that :

select * from var_data shud return like this :

ID Role

v1 1

v2 1

v2 2

v3 1

v3 3

v3 4

v3 5

Plz guide me how to achieve this result from the variable.

Thanks in advance :-)

This looks like a pretty good match for Jens Suessmeyer's split function with a CROSS APPLY join. Hang on and I'll get you an example.

Code Snippet

declare @.var varchar(40)
set @.var = 'v1#1@.v2#1,2@.v3#1,3,4,5'

select @.var

select left(a.splitValue, charindex('#', a.splitValue) - 1) as [ID],
b.splitValue as [Role]
from split(@.var, '@.') a
cross apply split(substring(splitValue, charindex('#', splitValue)+1, 99), ',') b

/*
ID Role
-- -
v1 1
v2 1
v2 2
v3 1
v3 3
v3 4
v3 5
*/

MVP Jens Suessmeyer's SPLIT function can be found at this post:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

extracting csv string and using in NOTIN clause

Hi,
I'm trying to pass a stored proc a list of values in a comma seperated strin
g:
("3125,3126,3129...") The values represent an integer column in a table.
Then I want to use the string to exclude the values from a SELECT as in:
CREATE PROCEDURE myProc
@.transactionIDs varchar(255)
AS
SELECT TransactionID FROM SalesTransactions
WHERE TransactionID NOT IN (@.transactionIDs)
GO
SQL Server complains that it can't cast a varchar to an int
Any suggestions on how to do this?
Can someone provide an example?
ThanksWhile someone might suggest you use dynamic SQL for this, I
wouldn't. Instead, consider either this not-so efficient solution:
select TransactionID
from SalesTransactions
where
charindex(
',' + rtrim(TransactionID) + ',',
',' + @.transactionIDs + ','
) = 0
-- be sure @.transactionIDs has no spaces
or, for greater efficiency, the more involved solution
that uses a table-valued function to convert @.transactionIDs
into a table of id values:
http://groups.google.co.uk/groups?q...wierzbicki+kass
You can then write:
select TransactionID
from SalesTransactions
where TransactionID not in (
select Item from ListTable(@.transactionIDs)
)
Steve Kass
Drew University
Opa wrote:

>Hi,
>I'm trying to pass a stored proc a list of values in a comma seperated stri
ng:
>("3125,3126,3129...") The values represent an integer column in a table.
>Then I want to use the string to exclude the values from a SELECT as in:
>CREATE PROCEDURE myProc
> @.transactionIDs varchar(255)
>AS
>SELECT TransactionID FROM SalesTransactions
>WHERE TransactionID NOT IN (@.transactionIDs)
>GO
>SQL Server complains that it can't cast a varchar to an int
>
>Any suggestions on how to do this?
>Can someone provide an example?
>Thanks
>
>
>