-3

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Tim
  • 1
  • 141
  • 372
  • 590
  • I don't really follow what you are asking here; why doesn't the above work for you? – Thom A Sep 06 '22 at 18:38
  • in a standalone SQL script, `@ConID` and `@PayNo` are not allowed – Tim Sep 06 '22 at 18:44
  • Why aren't they? Is it some arbitrary requirement from your application? Why doesn't it support parameterization? – Thom A Sep 06 '22 at 18:51
  • could you write it as a SQL script? Thanks. – Tim Sep 06 '22 at 19:01
  • You *have* a SQL script there already, you just need to complete the `SELECT` and `FROM` (which presumably you already have). So `SELECT ColumnName FROM dbo.Table1 T1 JOIN dbo.Table2 ON T1.SomeColumn = T2.SomeColumn WHERE ContractID = @ConID AND table2.Payment_Number = @PayNo;` Then all you need to is pass the values of your parameters (presumably from your application layer). – Thom A Sep 06 '22 at 19:05
  • @Lamu in t-sql, `@ConID` and `@PayNo` do not comply with its syntax. Is it to define a procedure and then to call it? – Tim Sep 06 '22 at 19:07
  • *"in t-sql, `@ConID` and `@PayNo` do not comply with its syntax"* That statement is completely false... The statement I provide above is completely valid. If you are getting errors about invalid syntax then post the *actual* SQL you have and the *actual* error you get when you run it. `DECLARE @I int = 1; SELECT @i; SELECT name FROM sys.databases WHERE database_id = @i;` will work fine, because the syntax with said variables is perfectly valid T-SQL. – Thom A Sep 06 '22 at 19:10
  • What does `@` mean? Is there a link that explain that? – Tim Sep 06 '22 at 19:25
  • Does this answer your question? [What does the "@" symbol do in SQL?](https://stackoverflow.com/questions/361747/what-does-the-symbol-do-in-sql) – Thom A Sep 06 '22 at 19:29
  • @Larnu how do you pass value to variable started with @? – Tim Sep 06 '22 at 19:43
  • 1
    That totally depends on your application layer. Google (or your favourite search engine) is your friend here. You could have saved a lot of time, and wasted a lot less, if youd started there with this question a lone. – Thom A Sep 06 '22 at 19:46
  • @Larnu i don't really know – Tim Sep 06 '22 at 19:46
  • @Tim is [this](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql?view=sql-server-ver16) the missing piece of the puzzle? – Dale K Sep 06 '22 at 20:32

1 Answers1

1

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 Schofield
  • 19,839
  • 3
  • 22
  • 35