CONversion of an input parameter for a SP in the desired form.

hi All ,

I am getting one param for a SP as list of states from the Front End as :

@.states = 'NY,NJ,CA,Fl,MA' . Now i have to convert this param in the form :

@.states_for_SP = 'NY','NJ','CA','Fl','MA' . Is there any efficient way to do it except using REPLACE function. As this portion in our SP is taking a lot of time in converting in the desired form.

Plz suggest to do this.

Thanks.

I don't understand why a simple replace, e.g.

Code Snippet

declare @.a varchar(100)
set @.a = '''NY,NJ,CA,Fl,MA'''

declare @.b varchar(100)
set @.b = replace(@.a, ',', ''',''')

select @.a, @.b

Gives 'NY,NJ,CA,Fl,MA' > 'NY','NJ','CA','Fl','MA'

Should be slow. Is this similar to what you're trying to do?

Greg.

|||

Mohit,

This is a slightly different approach.

Using the function below you can convert the list into a table and then join the table into your query.

Code Snippet

IFEXISTS(

SELECT*FROMsys.objects

WHEREobject_id=OBJECT_ID(N'[dbo].[list2set]')

ANDtypein(N'FN', N'IF', N'TF', N'FS', N'FT')

)

DROPFUNCTION [dbo].[list2set];

GO

CREATEFUNCTION dbo.list2set( @.list nvarchar(max), @.delim nvarchar(10))

RETURNS @.resultset TABLE( pos intidentity, item nvarchar(max))

AS

BEGIN

IFlen(@.list)<1 RETURN;

DECLARE @.xList XML;

-- no validity tests are performed, depending on input this could fail

SET @.xList =Convert(XML,''+REPLACE(@.list, @.delim,'')+'')

INSERTINTO @.resultset

SELECT data.listitem.value('.','nvarchar(max)')as item

FROM @.xList.nodes('/list/item') data(listitem)

RETURN

END

GO

You'd then use it as such:

Code Snippet

SELECT adr.state

FROM Address adr

innerjoin dbo.list2set(@.states, N',') sel

on adr.state = sel.item

|||

here the code..

Code Snippet

Create Table #Numbers(

Number Int

);

Declare @.I as int;

Set @.I = 1

While @.I<100

Begin

Insert Into #Numbers values(@.I);

Set @.I = @.I + 1;

End

Declare @.states varchar(100)

Set @.states = 'NY,NJ,CA,Fl,MA'

Declare @.StatesTable Table

(

State Varchar(100)

)

Insert Into @.StatesTable

Select Substring(',' + @.states + ',', Number, CharIndex(',',',' + @.states + ',',Number) - Number)

From

#Numbers

Where

Number<=Len(',' + @.states + ',')

And Substring(',' + @.states + ',',Number-1 ,1) = ','

--As you wise for concatination..

Set @.states = ''

Select @.states = @.states + ',''' + State + '''' From @.StatesTable

Select Substring(@.states,2,8000)

--Now You can use this @.StatesTable on any query for IN operator..

--Select * From SomeTable Where States in (Select State From @.StatesTable)

Consuming Stored Procedure Output Param

This is my SProc:

CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime

/* Input or Output Parameters */
/* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. */
/* as is this procedure will very well expect a value for @.numberRows */
@.selectDate datetime
,@.selectCountry int
,@.numberRows int OUTPUT

AS

SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_smc As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @.selectDate and RequestDateTime < dateadd(dd,1, @.selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @.selectCountry
ORDER BY configname

/* @.@.ROWCOUNT returns the number of rows that are affected by the last statement. */
/* Return a scalar value of the number of rows using an output parameter. */
SELECT @.numberRows = @.@.RowCount

GO

And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1

ProtectedSub BtnGetModels_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim dateEnteredAsString = TxtDate.Text

Dim selectCountryAsString = CountryList.SelectedValue

Dim conAsNew SqlClient.SqlConnection

con.ConnectionString ="Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"

Dim myCommandAsNew SqlClient.SqlCommand

myCommand.CommandText ="ap_Select_ModelRequests_RequestDateTime"

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.AddWithValue("@.selectDate", dateEntered)

myCommand.Parameters.AddWithValue("@.selectCountry",CInt(selectCountry))

Dim myParamAsNew SqlParameter("@.numberRows", SqlDbType.Int)

myParam.Direction = ParameterDirection.Output

myCommand.Parameters.Add(myParam)

myCommand.Connection = con

con.Open()

Dim readerAs SqlDataReader = myCommand.ExecuteReader()Dim rowCountAsInteger = reader.RecordsAffected

numberParts.Text = rowCount.ToString

con.Close()

EndSub

What should I fix?

label1.Text = myCommand.Parameters("@.numberRows").Value

|||

If I remember, I had this same problem, and found that you can't use the DataReader if you want to get the output parameter. I think you have to use DataSet.

|||

Read the following for an explanation of why it is happening and how to get around it.

http://p2p.wrox.com/archive/aspx/2001-12/24.asp

|||

How do I do the DataSet approach?

ProtectedSub BtnGetModels_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim dateEnteredAsString = TxtDate.Text

Dim selectCountryAsString = CountryList.SelectedValue

Dim conAsNew SqlClient.SqlConnection("Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx")

Dim dbDataSet =New DataSet()Dim dbAdapterAsNew SqlDataAdapter

dbAdapter.Fill(dbDataSet)

|||

You can do the following

Dim dbDataSet =New DataSet()
Dim dbAdapterAsNew SqlDataAdapter
dbAdapter.Fill(dbDataSet,"tablename")

dbDataSet.Tables("tablename").rows.count

In case you have only one table, you can use a datatable instead of a dataset

Dim dbDataTable =New DataTable()
Dim dbAdapterAsNew SqlDataAdapter
dbAdapter.Fill(dbDataTable)

dbDataTable.rows.count