April 15, 2010 at 11:39 pm
I have the following proc which is used in a report. Of these @rid is multi-valued. but in the report i am getting conversion from nvarchar to int error.
Could any one help?
CREATE PROC sp_UserPerformance (@rid bigint,@fromdate datetime,@todate datetime,@locid bigint)
AS
BEGIN
Select UserName as 'User Name'
,isnull(( Select Sum(HC_REQ_TEAM.Position)
From HC_REQ_TEAM WITH(NOLOCK)
WHERE HC_REQ_TEAM.TeamUserID=HC_USERS.RID
AND HC_REQ_TEAM.ReqID in(Select RID
From HC_REQUISITIONS WITH(NOLOCK)
WHERE HC_REQUISITIONS.ClientID in (16,15,13,14 ))
and HC_REQ_TEAM.ReqID in(Select ReqID
From HC_REQ_RESUME,HC_REQ_RESUME_STATUS WITH(NOLOCK)
WHERE HC_REQ_RESUME_STATUS.StatusDate between (@fromdate ) and (@todate)
and HC_REQ_RESUME.RID=HC_REQ_RESUME_STATUS.ReqResumeID ) ),'') as Openings
From HC_USERS WITH(NOLOCK)
WHERE HC_USERS.RID in (@rid)
AND HC_USERS.Locationid in (@locid)
Group By HC_USERS.RID,HC_USERS.UserName
END
April 15, 2010 at 11:49 pm
hi,
The @rid is taking the multi values by comma seperated....
ex: 12,45,24,3,78 ..
So the datatype int only takes single value of int only...it doesnot allow the above value...
For that change the @rid datatype to NVACHAR(<length/MAX>) or VARCHAR(<length/MAX>) in Ur SP.
Use the select stmt as...
Select rid,name,col2,col3 from tblename where rid in (Select fieldvalue from getorder(@rid))
getOrder table function code is availabale in my posts for Sort order....
look into that u should get clear picture that how to solve this issue...
For multi value selection u should use varchar/nvarchar datatype in the backend..
:-):-)
Sasidhar Chowdary
April 15, 2010 at 11:57 pm
hi,
Look into the following link for the function mentioned above
http://www.sqlservercentral.com/Forums/Topic900359-338-1.aspx#bm900398
April 16, 2010 at 12:01 am
Hi Shashidhar,
Thanks for the reply. I tried it. I changed @rid to varchar in both SP and my report. but still i am getting the same error.
if i run the query alone it works fine. but as SP, it is cusing prob.
so can you suggest me something? also, i didnt get your getorder() function.
Thanks,
Malavika
April 16, 2010 at 12:36 am
hi,
Its worked for me......
I dint get any error........
Execute the following code as it is....
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[Test_GetOrderTable] (@string varchar(max))
returns @tblOrder Table(id int identity(1,1),strings varchar(100))
AS
begin
declare @LocalVar varchar(100)
declare @pos int
set @pos=1
set @LocalVar=''
set @string = ltrim(rtrim(@string))
while (@pos <=len(@string))
begin
if (substring(@string, @pos,1)!=',')
begin
set @LocalVar =@LocalVar + substring(@string,@pos,1)
set @pos=@pos+1
end
else
begin
if @LocalVar <> ''
insert into @tblOrder values(ltrim(rtrim(@LocalVar)))
set @pos= @pos+ 1
set @LocalVar=''
end
end
if @LocalVar <> ''
insert into @tblOrder values(ltrim(rtrim(@LocalVar)))
return
end
GO
Run the following SP as it is....now test it...it should work.....
CREATE PROC sp_UserPerformance (@rid VACHAR(MAX),@fromdate datetime,@todate datetime,@locid bigint)
AS
BEGIN
Select UserName as 'User Name'
,isnull(( Select Sum(HC_REQ_TEAM.Position)
From HC_REQ_TEAM WITH(NOLOCK)
WHERE HC_REQ_TEAM.TeamUserID=HC_USERS.RID
AND HC_REQ_TEAM.ReqID in(Select RID
From HC_REQUISITIONS WITH(NOLOCK)
WHERE HC_REQUISITIONS.ClientID in (16,15,13,14 ))
and HC_REQ_TEAM.ReqID in(Select ReqID
From HC_REQ_RESUME,HC_REQ_RESUME_STATUS WITH(NOLOCK)
WHERE HC_REQ_RESUME_STATUS.StatusDate between (@fromdate ) and (@todate)
and HC_REQ_RESUME.RID=HC_REQ_RESUME_STATUS.ReqResumeID ) ),'') as Openings
From HC_USERS WITH(NOLOCK)
WHERE HC_USERS.RID in (SELECT Strings from Test_GetOrderTable(@rid))
AND HC_USERS.Locationid in (@locid)
Group By HC_USERS.RID,HC_USERS.UserName
END
Cheers!
Sasidhar Chowdary
April 16, 2010 at 1:44 am
Sasidhar Pulivarthi (4/15/2010)
hi,The @rid is taking the multi values by comma seperated....
ex: 12,45,24,3,78 ..
So the datatype int only takes single value of int only...it doesnot allow the above value...
For that change the @rid datatype to NVACHAR(<length/MAX>) or VARCHAR(<length/MAX>) in Ur SP.
I agree with what Sasidhar says about the need to change the data type to support a comma-delimited list.
But, please, don't use the code he posted for splitting the list. It is not set-based, and will perform very poorly when you hit it with a large record set.
See the following highly-performing, set-based example for how to split a delimited string apart into a table that can then be used to join into a table:
First, make a function that can be used.
CREATE FUNCTION dbo.DelimitedSplit8K
/***************************************************************************************************
Purpose:
Split a given string at a given delimiter and return a list of the split elements (items).
Usage Example:
SELECT *
FROM dbo.DelimitedSplit8K(@StringToSplit, @Delimiter)
Returns:
iTVF containing the following:
ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)
Item = Element value as a VARCHAR(8000)
Notes:
1. Optimized for VARCHAR(8000) or less.
2. Optimized for single character delimiter.
3. Optimized for use with CROSS APPLY.
4. Does not "trim" elements just in case leading or trailing blanks are intended.
5. cteTally concept originally by Iztek Ben Gan and "decimalized" by Lynn Pettis (and others) for a
bit of extra speed and finally redacted by Jeff Moden for a different slant on readability and
compactness. Extra speed realized (cuts out 9 SELECT/UNION ALL's) with UNPIVOT thanks to
Gianluca Sartori.
6. If you don't know how a Tally table can be used to replace loops, please see the following...
http://www.sqlservercentral.com/articles/T-SQL/62867/
Revision History:
Rev 00 - 20 Jan 2010 - Jeff Moden
- Base 10 redaction for CTE.
Rev 01 - 08 Mar 2010 - Jeff Moden
- Changed UNION ALL to UNPIVOT for bit of extra speed.
Rev 02 - 13 Mar 2010 - Jeff Moden
- Removed one additional concatenation and one subtraction from the SUBSTRING in the
SELECT List.
***************************************************************************************************/
--===== Define I/O parameters
(
@pString VARCHAR(8000),
@pDelimiter CHAR(1)
)
RETURNS TABLE
AS
RETURN
WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000).
E1(N) AS ( --=== Create Ten 1's very quickly
SELECT N
FROM (SELECT 1 N0, 1 N1, 1 N2, 1 N3, 1 N4, 1 N5, 1 N6, 1 N7, 1 N8, 1 N9) AS E0
UNPIVOT (N FOR Nx IN (N0, N1, N2, N3, N4, N5, N6, N7, N8, N9)) AS unpvt
), --10
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4)
--===== Do the split
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
FROM cteTally
WHERE N < LEN(@pString) + 2
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
;
Now, create a temp table with a delimited list, and with it call the function to split the list.
CREATE TABLE #List (List varchar(100))
INSERT INTO #List VALUES ('AB,CD,EF,GH,IJ,KL,MN,OP,QR,ST,UV,WZ,YZ')
DECLARE @Delimiter char(1);
set @Delimiter = ',';
select f.*
FROM #List l
CROSS APPLY DelimitedSplit8K(l.List, @Delimiter) f
See how the DelimitedSplit8K function returns a table that can be joined to (well, actually CROSS APPLYed to)? In Sasidhar's example, change:
From HC_USERS WITH(NOLOCK)
WHERE HC_USERS.RID in (SELECT Strings from Test_GetOrderTable(@rid))
To:
From HC_USERS
CROSS JOIN DelimitedSplit8K(@rid, ',')
Finally, clean up.
DROP FUNCTION dbo.DelimitedSplit8K
One last bit of advice... drop those "WITH (NOLOCK)" hints.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 16, 2010 at 4:45 am
Thanks Wayne. I am actually running the rep thru query but will soon change it to SP based on your hints.
And abt that WITH(NO LOCK), i am not sure why it is used. i am new here and in each and every query they have written it.
can you explain what is it and wht are its merits and de-merits?
And I have one more prob now:
I have a report which calls another report for details.
(like i have given the "jump to report" option.
Now i am not getting how to match the parameters between the reports.
In my Recruitment report, we will display the total vacancies in a particular co.
in the detail report, we will display the requirement title, no. of posts open, exp reqd and so on.
Now company id of my master report must match with that of detail. how to do?
April 16, 2010 at 6:56 am
Malavika, NO LOCK is a TABLE HINT which instructs Query Optimizer (QO) to do a thing in a different way from the way QO actually planned to do..
You can learn more about TABLE HINTS by clicking this TABLE HINTS
Cheers!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply