October 31, 2007 at 5:10 am
Hi All,
I want to pass string value in a stored proc and use it as integer
i.e i want to get the data that belongs to different cities in a single proc
CREATE Proc usp_GetTotalRecords
@iisrid int,
@iuserid int,
@sPregionCities int,
@iCampid int,
@iTotalrecord int Output
As
--Select data From All Campaign
Begin
Select @iTotalrecord=count(*) from customermaster P
where p.icustomerid in
(select top 500 icustomerid from prospectsmaster where ((iprospstatus=6 and iReason not in (2,3,8) or iprospstatus=3)
and iisrid=@iisrid and icityid in (@sPregionCities) and iuserid=@iuserid))
return @iTotalrecord
the variable @sPregionCities has value "10013,10014"
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 31, 2007 at 8:01 am
I completely Concur with Johannes's solution where you just need to use a function that returns all values after applying the comma delimiting logic and insert the same into a temp table or a table variable and include in clause over values in the temp table.
The second option is using dynamic sql which is not recommended and not advised inview of sql injection issues and performance contraints however if you intend to take a look at it. you can try out this.
ALTER Proc usp_GetTotalRecords
@iisrid int,
@iuserid int,
@sPregionCities varchar(100),
@iCampid int
As
--Select data From All Campaign
Begin
DECLARE @strSQL VARCHAR(2000)
SET @strSQL = 'Select count(*) AS iTotalrecord from customermaster P
where p.icustomerid in
(select top 500 icustomerid from prospectsmaster where ((iprospstatus=6 and iReason not in (2,3,8) or iprospstatus=3)
and iisrid=' + CAST(@iisrid AS VARCHAR) + ' and icityid in (' + @sPregionCities + ') and iuserid=' + CAST(@iuserid AS VARCHAR) + '))'
EXEC (@strSQL)
END
Prasad Bhogadi
www.inforaise.com
October 31, 2007 at 11:32 am
Here is how you can accomplish Prasad's first reccomendation:
CREATE Proc usp_GetTotalRecords
@iisrid int,
@iuserid int,
@sPregionCities varchar(200),
@iCampid int,
@iTotalrecord int Output
As
declare @iStart int,
@iPosint
declare @tblArray (iZip int)
IF RIGHT(@sPregionCities, 1) <> ','
SET @sPregionCities = @sPregionCities + ','
SET @iStart = int
SET @iPos = CHARINDEX(',', @sPregionCities, @iStart)
WHILE @iPos > 0
BEGIN
INSERT INTO @tblArray
SELECT SUBSTRING(@sPregionCities, @iStart, @iPos - @iStart)
SET @iStart = @iPos + 1
SET @iPos = CHARINDEX(',', @sPregionCities, @iStart)
END
--Select data From All Campaign
Select @iTotalrecord = count(*)
from customermaster P
where p.icustomerid in (select top 500 icustomerid
from prospectsmaster pm
where ((iprospstatus=6 and iReason not in (2,3,8) or iprospstatus=3)
and iisrid=@iisrid
and EXISTS (SELECT NULL FROM @tblArray WHERE iZip = pm.icityid)
and iuserid=@iuserid))
return @iTotalrecord
October 31, 2007 at 9:51 pm
thanks,
is array is supported in SQL server 2000.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 31, 2007 at 10:08 pm
No, arrays are not supported. He is using a table variable and he just happened to use the word array in the table variable name. I would recommend creating numbers table as previously suggested and then creating a split function. You can get many examples of working split functions by searching the script area of SSC. Many of them use procedureal logic to do the split, but here's the one I use that takes advantage of the numbers table.
CREATE FUNCTION dbo.udf_Split (@parmString VARCHAR(8000), @parmDelim varchar(10))
RETURNS TABLE
AS
RETURN (
SELECT Num,
RTRIM(LTRIM(SUBSTRING(@parmString,
CASE Num
WHEN 1 THEN 1
ELSE Num + 1
END,
CASE CHARINDEX(@parmdelim, @parmString, Num + 1)
WHEN 0 THEN LEN(@parmString) - Num + 1
ELSE CHARINDEX(@parmdelim, @parmString, Num + 1) - Num -
CASE
WHEN Num > 1 THEN 1
ELSE 0
END
END
))) AS ListItem
FROM dbo.Numbers
WHERE Num <= LEN(@parmString)
AND (SUBSTRING(@parmString, Num, 1) = @parmdelim
OR Num = 1)
Then simply use the function as a table in your code.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply