How can I convert the parameters, when converting a dataset query into a SQL script?

Tim 0
Viewed 64

In a SSRS data set, the query is like:

SELECT ...

FROM ...

WHERE        
    (table1.ContractID = @ConID)
    AND
    (table2.Payment_Number = @PayNo)

I would like to write a T-SQL script which performs the query, but was wondering how to convert the two parameters ConID and PayNo in the dataset?

Is it to define a procedure in the T-SQL script with the two parameters, and call it?

Thanks.

reporting-services tsql sql-server
1 Answers

I think I know what you are asking...

In an SSRS dataset query you might have something like

SELECT * FROM Sales WHERE CompanyID = @Company

When this is executed in SSRS any undeclared variables are passed in from the the report parameter(s) to the script and it is executed.

If you just want a script to do the same thing then you need to declare the variable. So it would just be

DECLARE @Company INT = 1234
SELECT * FROM Sales WHERE CompanyID = @Company

Typically, when you write a script you would always declare the variables, it's only when you want to use the script directly in a dataset query that you should not declare them.

Alan Schofield0
Related