how do I assign a string to a parameter Im passing to a select statement?

Hello,

I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product.

I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here:

http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx

I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.

I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:

WHERE (OrderStatus IN ((select value from dbo.fn_Split(@.StatusList,','))) OR @.StatusList IS NULL)

but now I don't know how to assign the string value to the parameter, say to '@.solution_id'.

my current select statement which was generated by Visual Studio 2005 looks like this:

SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @.solution_ID)

...but this only pulls results for the first item checked in the checkboxlist.

Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.

hi

First make sure you have createddbo.fn_Split .

SELECT [test], [owner], [date]FROM [test_table]WHERE ([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)

I am not sure "OR @.solution_IDISNULL" should be added,you have to decide it according to your logic.

You are required to pass @.solution_ID to the statement(1,3,4,6 etc) then you can get corresponding test.

Hope this helps.

|||

Thanks for your response. If I'm following you, I do understand that I need to pass @.solution_ID to the select statement like you showed. I have a string of values that I created from iterating through CheckBoxList to find selected boxes. My question is, how do I assign the value of this string to @.solution_ID?

Regards,

Daniel

|||

Assume you have checkboxlist Check1, using following code to get @.solution_ID :

for (int i = 0; i < Check1.Items.Count; i++)
{
if (Check1.Items[i].Selected)
{
// List the selected items
solution_ID = solution_ID + Check1.Items[i].Text;
solution_ID = solution_ID +",";
}
}

Then connect with DB:

SqlCommand sqlcmd = new SqlCommand("SELECT [test], [owner], [date]FROM [test_table]WHERE
([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)", sqlconn);
sqlcmd.Parameters.AddWithValue("@.solution_ID",solution_ID);
sqlconn.Open();
SqlDataReader sdr = sqlcmd.ExecuteReader();

.............

You 'd bette put bold sql script into a stored procedure.

hope this helps.

how create table with variable name

I want to create a table with variable name but can't? how
declare @.k as char(100)
set @.k = 't1'
create table @.k << create table with name=@.k
thanks
tarvirdiDECLARE @.OrderCounts TABLE(ProductID int, OrderCount int)
INSERT @.OrderCounts values (1, 1)
select * from @.OrderCounts
"Tarvirdi" <m_tarvirdi@.isc.iranet.net> wrote in message
news:%23PJ4AJsjGHA.5020@.TK2MSFTNGP02.phx.gbl...
>I want to create a table with variable name but can't? how
> declare @.k as char(100)
> set @.k = 't1'
> create table @.k << create table with name=@.k
> thanks
> tarvirdi
>|||Use dynamic SQL to create the table (using youe example):
declare @.k as char(100)
declare @.sql as varchar(200)
set @.k = 't1'
set @.sql = 'create table ' + @.k
EXEC(@.sql)
"Tarvirdi" wrote:

> I want to create a table with variable name but can't? how
> declare @.k as char(100)
> set @.k = 't1'
> create table @.k << create table with name=@.k
> thanks
> tarvirdi
>
>

How can you use a variable tablename and retrieve the output from the Insert?

We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.

When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @.tTemp.

How can you use a variable table name and retrieve the output from the Insert?

declare @.tTestTable varchar (20)

set @.tTestTable = 'mis.test_sequence'

--DECLARE @.tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)

declare @.testsql varchar (4000), @.testseq int

DECLARE @.tTemp table (mainpk int)

set @.testsql = 'DECLARE @.tTemp table (mainpk int) INSERT ' + @.tTestTable + ' OUTPUT INSERTED.sqVal into @.tTemp VALUES (getdate() ) SELECT @.testseq=mainpk FROM @.tTemp'

select @.testsql

EXECUTE sp_executesql @.testsql, N'@.testseq int output,@.tTemp table (mainpk int),@.tTemp table (mainpk int) ',@.tTemp,@.tTemp,@.testseq output,@.tTemp

SELECT * FROM @.tTemp

Please help

Thanks Tim.

Why not to create an sp per each table, instead trying to come with a general one?

Code Snippet

use tempdb

go

create table #t (c1 int not null identity, c2 datetime)

declare @.tTestTable varchar (20)

set @.tTestTable = '#t'

declare @.testsql nvarchar (4000), @.testseq int

set @.testsql = 'INSERT into' + quotename(@.tTestTable) + '(c2) values(getdate()); set @.testseq = scope_identity()'

select @.testsql

EXECUTE sp_executesql @.testsql, N'@.testseq int output',@.testseq output

SELECT @.testseq

drop table #t

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB|||

Thankyou hunchback,

Your Code Snippet helped me solve my problem.

Tim.

Here's my final code.

USE [TestDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create proc GetNext (@.sequenceName varchar(40) , @.nextVal int output)

as

begin

declare @.sqlStmt nvarchar (4000)

declare @.tTemp table (mainpk int)

set nocount on

-- This option works using the OUTPUT clause.

set @.sqlStmt = 'DECLARE @.tTemp table (mainpk int) Insert ' + @.sequenceName + ' OUTPUT INSERTED.sqVal into @.tTemp DEFAULT VALUES SELECT @.nextVal=mainpk FROM @.tTemp'

execute sp_executesql @.sqlStmt, N'@.nextVal int output',@.nextVal output

set nocount off

end

How can pass variable or parameter in DATA READER SOURCE ising ADO:NET Connection manager

In SSIS in Sql task we have option to pass parameter or variable..But in Data Flow Task when we use Data Reader Source using ADO.NET connection..There is no option to pass parameter or variable Or no option to receive a parameter or variable .

I am having a query were it need to pass a parameter.in sql task ...And Data Reader Source have to receive this parameter from sql task .

Sql Task finds a value of parameter and pass to DataReader Source in DataFlow Task .. ...

Please can any one help me to solve this problem of Receiving parameter or variable in DataReader Source using DAO.Net connection in DataFlow Task..thank you dilsa

USe an expression to override SQLCommand porperty of the data reader:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1283829&SiteID=1

How can I write this SQL statement when the string value is a variable

Hi. I was wondering how I might be able to write the following SQL statement | SET @.AlertSymbol = N'MSFT' | when I want to replace the MSFT with the variable @.StockSymbol?

I'd like to do something like | SET @.AlertSymbol = N'@.StockSymbol' | but that doesn't seem to work as SQL isn't evaluating @.StockSymbol but rather treating it as a string.

All of the following return errors:

SET @.AlertSymbol = N@.StockSymbol
SET @.AlertSymbol = N+@.StockSymbol
SET @.AlertSymbol = N&@.StockSymbol

Thanks, MattJust declare @.StockSymbol as nchar or nvarchar and you can simply use

SET @.AlertSymbol = @.StockSymbol

The N in N'foo' tells SQL Server that 'foo' is in unicode. The same applies to all n<bar> datatypes.|||Originally posted by mt404
Hi. I was wondering how I might be able to write the following SQL statement | SET @.AlertSymbol = N'MSFT' | when I want to replace the MSFT with the variable @.StockSymbol?

I'd like to do something like | SET @.AlertSymbol = N'@.StockSymbol' | but that doesn't seem to work as SQL isn't evaluating @.StockSymbol but rather treating it as a string.

All of the following return errors:

SET @.AlertSymbol = N@.StockSymbol
SET @.AlertSymbol = N+@.StockSymbol
SET @.AlertSymbol = N&@.StockSymbol

Thanks, Matt

Nchar/varchar has higher precedence than char/varchar. Hence, an implicit conversion should take care of this for you.

e.g.

declare @.AlertSymbol nvarchar(10),
@.StockSymbol varchar(10)

set @.StockSymbol='MSFT'

set @.AlertSymbol=@.StockSymbol

--sql2k
select sql_variant_property(@.AlertSymbol,'BaseType'), @.AlertSymbol|||Thaks to both of you for helping me out and teaching me what the N'foo' actually meant.

How can i view the value of a variable during debug?

Thats the question... i can see the value of columns adding viewers, but how can i see variables?

Thanks!

Add a break point to one of your control flow tasks (by right clicking on it) and then when debug stops at that break point, you can go up to the Debug menu and select the Locals window to be displayed.|||

Thanks Phil,

I do that, but my variable doesnt appear in the window...

|||

Which window are you looking at? It should be in the Locals window; you have to expand the variables node...

|||

Sorry for my delay rafael, i was on holidays ;-)

I am looking at "Debug/windows/local variables", but nothing appears in this window.

Edit: I finally found the solution, the problem was taht i didnt insert the break point on control flow. Regards.

How can i view the value of a variable during debug?

Thats the question... i can see the value of columns adding viewers, but how can i see variables?

Thanks!

Add a break point to one of your control flow tasks (by right clicking on it) and then when debug stops at that break point, you can go up to the Debug menu and select the Locals window to be displayed.|||

Thanks Phil,

I do that, but my variable doesnt appear in the window...

|||

Which window are you looking at? It should be in the Locals window; you have to expand the variables node...

|||

Sorry for my delay rafael, i was on holidays ;-)

I am looking at "Debug/windows/local variables", but nothing appears in this window.

Edit: I finally found the solution, the problem was taht i didnt insert the break point on control flow. Regards.

how can I use (contains) in stored procedeure?

how can I use:

where field_name LIKE '%' + variable + '%'

in stored procedure?

I tried field_name = %@.variable% but didn't work..

field_name LIKE '%' + variable + '%' should work, exactly as you have it.

Can you post more of the code that is not working?

|||

Use

where field_name LIKE '%' + @.variable + '%'

instead of

where field_name LIKE '%' + variable + '%'

You need to add the "@." character to your variable name.

Regards,

|||

this is the error I am getting followed by the stored procedure:

The resource cannot be found.

Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly.

Requested URL: /Special Numbers/www.yahoo.com

the Stored Procedure:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [shopnumbers].[sp_get_latest_number]

-- Add the parameters for the stored procedure here

-- <@.Param1, sysname, @.p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

-- <@.Param2, sysname, @.p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

@.country_code int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

-- SELECT category.[category_english_name] + ' - ' + category.[sub_category_english_name] AS full_category_name, numbers.the_number, numbers.created_date FROM owners INNER JOIN (category INNER JOIN numbers ON category.category_id = numbers.category_id) ON owners.owner_id = numbers.owner_id;

SELECT numbers.number_id, numbers.number_guid, country.country_flag, numbers.category_id, numbers.the_number, numbers.created_date, numbers.amount, country.country_english_name, category.category_english_name + ' - ' + category.sub_category_english_name AS full_category_name

FROM numbers

INNER JOIN country ON numbers.country_id = country.country_id

INNER JOIN category ON numbers.category_id = category.category_id

WHERE (numbers.number_disabled <> 'Y') AND country.country_code LIKE '%' + @.country_code + '%'

END

|||

OK.. I know where is the problem.. it was a varChar and I changed it to Int which dosn't allow LIKE.

Thank's any way..

Happy holidays..