Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SP error Expand / Collapse
Author
Message
Posted Thursday, April 15, 2010 11:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 15, 2011 12:09 AM
Points: 140, Visits: 748
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
Post #904565
Posted Thursday, April 15, 2010 11:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:36 AM
Points: 1,063, Visits: 709
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
Post #904567
Posted Thursday, April 15, 2010 11:57 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:36 AM
Points: 1,063, Visits: 709
hi,

Look into the following link for the function mentioned above

http://www.sqlservercentral.com/Forums/Topic900359-338-1.aspx#bm900398
Post #904569
Posted Friday, April 16, 2010 12:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 15, 2011 12:09 AM
Points: 140, Visits: 748
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
Post #904570
Posted Friday, April 16, 2010 12:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:36 AM
Points: 1,063, Visits: 709
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
Post #904580
Posted Friday, April 16, 2010 1:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:59 PM
Points: 5,358, Visits: 8,919
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #904602
Posted Friday, April 16, 2010 4:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 15, 2011 12:09 AM
Points: 140, Visits: 748
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?
Post #904680
Posted Friday, April 16, 2010 6:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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!!
Post #904800
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse