October 20, 2005 at 8:02 am
In my stored procedure, I am inputting a string of letters and numbers separated by commas. Then in the WHERE clause I am asking it to get the Item_Key out of this string. Here is the WHERE clause:
WHERE (Item_Key IN (@strItems))
Item_Key is a bigint and the @strItems is varchar. I am connecting through an Access Project and it is having trouble converting the varchar to a bigint. It is correctly bring back the Item_Key, but the error says it can't convert varchar to bigint. How can I convert this?
Also, it is bringing back the item_key as, for example: "1850," - is the problem that the comma is being brough back as well? 1850 is a correct item_key.
Thanks!
October 20, 2005 at 8:16 am
IF EXISTS (Select * from dbo.SysObjects where name = 'Numbers' and XType = 'U' and user_name(uid) = 'dbo')
DROP TABLE Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
DECLARE @Ids as varchar(8000)
SET @IDS = ''
Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds
SET @IDS = left(@IDS, ABS(len(@IDS) - 1))
PRINT @IDS
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank
--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.
October 21, 2005 at 9:23 am
You may also want to look into the following link which discusses a similar situation:
http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx
October 21, 2005 at 11:38 am
Or since you have already gone to the trouble of building the list of items that you can search for in @stritems
where charindex(Convert(varchar(20), item_key) + ',', @stritems) > 0
convert the BigInt to a string, add a comma and then search string to string. if charindex = 0 the Item_Key was not found. this assumes that ALL the values in @stritems are followed by a comma, including the last one.
The Table method above is way faster and would be generally preferred, but if you have inherited a mess from one of your beloved vendors, this will get you thru.
October 21, 2005 at 11:40 am
You might also wanna read this for more info.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply