Passing parameter from a webpage to a report

I am developing a website which need to allow user to pass through several webpages for criteria selection and generate a report base on the selected criteria finally.

Anyone know how to pass the selected value from a webpage to the SQL statement or stored procedure which used to generate the report? Or there are other methods to do so?look at the rendering methods of RS - there's one by URL where you can pass the parameters via URL and request the report.|||I believe you simply build a querystring and append the parameters and values to the end of the querystring.|||Thanks for your kindly reply.

I have another related question see whether anyone can help. Base on my current knowledge, I know that we can pass one value for each parameter. E.g. countryID=20. If I want to pass multiple values for this parameter, e.g. countryID=20, 21, 25, ... How can I do so? Also, how should I set it in report designer for this purpose?|||Can you explain little more in detail (perhaps with some sampoe) ? Is your data in the format 20,21,25 for each record ?|||You could pass in a string like you describe. You would need to have logic in your report or stored procedure to handle such a request, though...|||In my report, there is a chart to compare sales performance between several products which are selected by user. The number of selected products is not fixed. I need to pass multiple productID into the report.

Also, I face a problem that ...seem I can't pass parameters into SQL functions to build dataset. Is it a rule? The error msg is "Syntax error or access violation.". My statement is as follows:

select * from getTable(@.productID)

Notes: getTable is a user-defined function.|||so what u can do

Select * from table where productid in (@.productid)|||In the following statement, getTable is a function with many calculation.

select * from getTable(@.productID)

If I use the one below, I can't get what I want. Also, seem can't be a string e.g. "12, 13, 14"
Select * from table where productid in (@.productid)|||so just pass the value in paenthesis

like '12,13,14'. it will definitely work.

If it is a function. then it should return the value same as u mentioned like "12,13,14"
if u can make it " '12,13,14' " . Then you can pass on values.

Pass a variable to SET IDENTITY_INSERT

I'm quite new to programming with SQL Server.
I'm developing an SQL statement using Query Analyzer that read a table from a MSAccess Linked server database, and put the data in to a SQL server table.

I'd like that table name, db name, etc, are values stored in some variables, so, when i want to import another table, i need to change only the value of the @.table_name variable.

I can pass the value of this variable to all the statements of my script but one. I'm able to execute something like

EXEC ('select * from' + @.table_name + '... bla bla bla')

but i'm not able to execute the SET IDENTITY_INSERT @.table_name ON/OFF.
I tried many options like print, exec, quotes, double quotes, etc, but nothing to do.

I'd really appreciate all your helping answers.

Ciao

Darioif you wish to do this you will need to build a string containing the set statement and the insert statment. then EXEC(@.string).

Remember that EXEC() runs on a seperate thread than your current connection so any settings will be lost once the EXEC finishes.

declare @.TSQL varchar(1000)
CREATE TABLE #products (id int IDENTITY PRIMARY KEY, product varchar(40))

-- Inserting values into products table.
INSERT INTO #products (product) VALUES ('screwdriver')
INSERT INTO #products (product) VALUES ('hammer')

select * From #Products

-- Inserting ids and values into products table.
set @.TSQL = 'set identity_insert #products on
INSERT INTO #products (id,product) VALUES (10,''saw'')
INSERT INTO #products (id,product) VALUES (11,''shovel'')'

exec(@.TSQL)

select * from #products

drop table #Products|||The script is the following:

-- Variable declaration
DECLARE @.nome_db nvarchar(50)
DECLARE @.nome_tabella nvarchar(50)
DECLARE @.id_tabella int
DECLARE @.str_nome_campi nvarchar(255)
DECLARE @.str_tmp_nome_campo nvarchar(255)
DECLARE @.strdata nvarchar(255)
DECLARE @.str_sql nvarchar(255)
DECLARE @.str_sql_delete nvarchar(255)
DECLARE @.str_sql_use nvarchar(255)
DECLARE @.str_sql_debug nvarchar(255)
DECLARE @.str_id_insert_on nvarchar(50)
DECLARE @.str_id_insert_off nvarchar(50)

-- Set the variables
SET @.nome_db = 'Unitec_Script_Importazione'
SET @.nome_tabella = 'ANFRAGEDETAILS2'

SET @.str_sql_delete = 'DELETE FROM [' + @.nome_db + '].[dbo].[' + @.nome_tabella + ']'
SET @.str_sql_use ='USE [' + @.nome_db + ']'
SET @.str_sql_debug = 'SELECT TOP 10 * FROM ' + @.nome_tabella

-- Stringhe per settare Identity ... maledette!
SET @.str_id_insert_on = ' SET IDENTITY_INSERT ' + quotename(@.nome_tabella) + ' ON' + char(10)
SET @.str_id_insert_off =' SET IDENTITY_INSERT ' + quotename(@.nome_tabella) + ' OFF' + char(10)
-- Trovo, all'interno della tabella di sistema sysobjects, l'ID
-- della tabella. Usando questo valore posso trovare i campi relativi
SET @.id_tabella = (SELECT [id] FROM sysobjects WHERE [name] LIKE @.nome_tabella)
--SELECT @.id_tabella -- (debug - stampa il risultato della query)

DECLARE nome_campi CURSOR
FOR SELECT [name] FROM syscolumns where id = @.id_tabella
-- Open the cursor
OPEN nome_campi
-- Prendo la prima riga del cursore e la schiaffo dentro alla variabile temporanea
FETCH NEXT FROM nome_campi INTO @.str_tmp_nome_campo

SET CONCAT_NULL_YIELDS_NULL OFF

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.str_nome_campi = @.str_tmp_nome_campo
SET @.strdata = @.str_nome_campi + @.strdata
FETCH NEXT FROM nome_campi INTO @.str_tmp_nome_campo
-- Aggiungo la virgola se necessario
SET @.str_tmp_nome_campo = @.str_tmp_nome_campo + ', '
END

SELECT @.strdata -- Debug: stampa il contenuto di strdata

CLOSE nome_campi
DEALLOCATE nome_campi

EXECUTE (@.str_sql_use)

EXECUTE sp_executesql @.str_sql_delete

/******* THIS IS WHERE I NEED HELP*********
Instead of the name of the table (ANFRAGEDETAILS2)
i want to use a variable @.table_name
*****************************************/
SET IDENTITY_INSERT ANFRAGEDETAILS2 ON

SET @.str_sql = @.str_sql + 'INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select ' + @.strdata + ' from [' + @.nome_tabella + ']'')'

EXECUTE (@.str_sql)

SET IDENTITY_INSERT ANFRAGEDETAILS2 OFF

--
--
--|||have you tried:

SET @.str_sql = @.str_sql + 'SET IDENTITY_INSERT ANFRAGEDETAILS2 ON
INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select ' + @.strdata + ' from [' + @.nome_tabella + ']'')
SET IDENTITY_INSERT ANFRAGEDETAILS2 OFF'
EXECUTE (@.str_sql)|||Yes, 'SET IDENTITY_INSERT ANFRAGEDETAILS2 ON ... works
but 'SET IDENTITY_INSERT @.table_name ON ... Doesn't work|||AND
SET @.str_sql = @.str_sql
+ ' SET IDENTITY_INSERT [dbo].['+@.nome_tabella+'] ON '
+'INSERT INTO [dbo].[' + @.nome_tabella + '] (' + @.strdata + ') SELECT * FROM OPENQUERY(Anfragen, ''Select '' + @.strdata + '' from ['' + @.nome_tabella + '']'')'
EXECUTE (@.str_sql)
WORKS !?|||Yes, it works!!!

Many thanks

Dario