Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server
hi all,
 
i am write the query in sql server working fine, but how to implement the same query using procedures.
 
my sql query like this,
 
SELECT RECORDID,AVAILABILITY,EMPLOYEMENTSTATUS,CLIENTNAME,DOB,SKILLS,EMAILID,VISA,CREATEDDATE,LOCATION,CITY+', '+C_STATE as LOCATION FROM dbo.RECORDDETAILS where CLIENTNAME in ('newclient','gdgf','ibm','pp')
 
now am create the Procedure 'sp_client'
create procedure sp_client(@clientname)
as
begin
SELECT RECORDID,AVAILABILITY,EMPLOYEMENTSTATUS,CLIENTNAME,DOB,SKILLS,EMAILID,VISA,CREATEDDATE,LOCATION,CITY+', '+C_STATE as LOCATION FROM dbo.RECORDDETAILS where CLIENTNAME in (@clientname)
end
 
how to execute my procedure, i have only one parameter but how to pass multiple client names in single parameter
 
please help me,
thanks and regards
Posted 30-Jan-13 1:28am
Edited 30-Jan-13 1:55am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

One way is to use dynamic sql. Goes like this:
 
DECLARE @query varchar(2000) 
SET @query = 'SELECT RECORDID,AVAILABILITY,EMPLOYEMENTSTATUS,CLIENTNAME,DOB,SKILLS,EMAILID,VISA,CREATEDDATE,LOCATION,CITY+', '+C_STATE as LOCATION FROM dbo.RECORDDETAILS where CLIENTNAME IN (' + @clientname + ')'
EXECUTE (@query)
 
Hope this helps!
  Permalink  
Comments
SChristmas at 30-Jan-13 7:47am
   
Good one
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

create procedure sp_client(@clientname)
as
begin
Declare @Str as Nvarchar(Max)
Set @Str = 'SELECT RECORDID,AVAILABILITY,EMPLOYEMENTSTATUS,CLIENTNAME,DOB,SKILLS,EMAILID,VISA,CREATEDDATE,LOCATION,CITY+'', ''+C_STATE as LOCATION FROM dbo.RECORDDETAILS where CLIENTNAME in (' + @clientname +')'
Print(@Str)
Exec(@Str)
end
 
Remember that your @clientname must be in below form.
 
clientname = 'value1','value1','value3','value4'
 
I mean each value must having single cot.
  Permalink  
Comments
Santhosh23 at 30-Jan-13 10:02am
   
i tried on this format, i got error procedure pass the too many multiple parameters, how to pass the 'value1','value1','value3','value4' this way
Mr. Mahesh Patel at 30-Jan-13 23:31pm
   
when at .net side i this you concate string using comma (,)
at that time you also have to concate with Single quate ('')
 
like
 
string value1 = "Mahesh";
string value2 = "Santhosh";
 
string str = "'"; ////////////Declare With single quate in starting
str += value1 + "','" ///////Append value1 with single quate and comma
str += value2 + "','" ///////Append value2 with single quate and comma
// now string becomes like this
// "'Mahesh','Santhosh','"
 
str = str.substring(0,str.length-2) /////Remove last 2 char which is ,' (comma and single quate) while you have written above code in loop.
 
// now string becomes like this
// "'Mahesh','Santhosh'"
 
This above string will not give error at SQL Side
try it
 
Mr. Mahesh Patel at 30-Jan-13 23:32pm
   
if you have any doubt, please place your .net code over here from where you are concate string. and send to sql.
then i will provide some solution
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

you can pass the clientname as comma seperated string as follows
'AA,BBB,CCC'
 
split that as a table and use to the IN query.
 
You can use following table function to split and create the table from your comma separated string
CREATE FUNCTION [dbo].[FT_CSVToTable] 
(@CSVList varchar(8000) )
RETURNS 
    @tIDList TABLE  (ClientName varchar(100) )
AS
BEGIN
 
          WITH CSVCte (StartPos, EndPos) AS 
                   ( SELECT 1 AS StartPos, CHARINDEX(',' , @CSVList + ',') AS EndPos
                             UNION ALL
                                      SELECT EndPos + 1 AS StartPos , CHARINDEX(',',@CSVList + ',' , EndPos + 1) AS EndPos FROM CSVCTE 
                                                WHERE CHARINDEX(',', @CSVList + ',', EndPos + 1) <> 0)
 
          INSERT INTO @tIDList  
                   SELECT SUBSTRING(@CSVList, StartPos,EndPos-StartPos) FROM CSVCte
 
   RETURN 
END 
 
Then change your query as follows
SELECT RECORDID,AVAILABILITY,EMPLOYEMENTSTATUS,CLIENTNAME,DOB,SKILLS,EMAILID,VISA,CREATEDDATE,LOCATION,CITY+', '+C_STATE as LOCATION 
FROM dbo.RECORDDETAILS where CLIENTNAME in (SELECT ClientName  FROM dbo.FT_CSVToTable(@clientname))
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

You should use one of my CLR to split the string into a table, after that you can join for that table and you can keep the execution plan.
 
CLR-addon for DATE and Split-String[^]
 

declare @client_names varchar(max) = 'newclient,gdgf,ibm,pp'
 
<pre lang="sql">select cast(Item as varchar(128)) as CLIENTNAME
into #tmp
from dbo.clr_split2string(@client_names, ',')
 

 
SELECT RECORDID,AVAILABILITY,EMPLOYEMENTSTATUS,CLIENTNAME,DOB,SKILLS,EMAILID,VISA,CREATEDDATE,LOCATION,CITY+', '+C_STATE as LOCATION
FROM dbo.RECORDDETAILS rd
join #tmp tmp on tmp.CLIENTNAME = rd.CLIENTNAME
 

 
you may join directly the clr, but I suggest to put in temp table first because you can use the statistics in this way.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Nirav Prabtani 304
1 OriginalGriff 237
2 Sergey Alexandrovich Kryukov 196
3 Mika Wendelius 185
4 CPallini 170
0 Nirav Prabtani 284
1 OriginalGriff 217
2 Sergey Alexandrovich Kryukov 196
3 Mika Wendelius 185
4 CPallini 170


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 31 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100