How to correctly pass string as a parameter

Question

I want to pass 'H18E142' as a string in a stored procedure. This parameter will then be used in a dynamic SQL select statement but I have an error saying 'H18E142' is an Invalid column name.

I've placed 'H18E142' within the SQL code directly and it works but when I pass as an absolute valued directly in the dynamic SQL

-- This is the select statement -- The char(7) value being passed in variable @EIRCODE is 'A63DH00'

     SELECT '''  + @quarterForDbName + ''' AS Quarter, ADDR_LINE_1, ADDR_LINE_2, ADDR_LINE_3, ADDR_LINE_4 FROM EDB_'+ @quarterForDbName +'.dbo.POSTAL_ADDRESS PA WHERE PA.EIRCODE = ' + @EIRCODE

The expected results returned are the following fields populated for whatever postcode is passed:

-Current quarter -Address line 1 -Address line 2 -Address line 3 -Address line 4

Why pass the *database* name as a parameter instead of simply connecting to the correct database? You can't even use a table name as a parameter, much less a database. As for `@EIRCODE`, just use the parameter, don't append it to the query. Your query should look like `SELECT @quarterForDbName AS Quarter, ADDR_LINE_1, ADDR_LINE_2, ADDR_LINE_3, ADDR_LINE_4 FROM dbo.POSTAL_ADDRESS PA WHERE PA.EIRCODE = @EIRCODE` — Panagiotis Kanavos, Jun 26 '19 at 10:25
BTW what's the point of a per-quarter *database*? Even partitioning would be redundant unless you have many millions of rows. — Panagiotis Kanavos, Jun 26 '19 at 10:28
Thanks for the reply. The reason for the database variable is that there are many databases for each quarter and they are being accessed using a while loop. This database set up I have inherited. — Cinneach, Jun 26 '19 at 10:49
That doesn't prevent you from connecting to the correct database as needed. In any case, *don't* concatenate the contents of `@EIRCODE`, use the parameter in the query itself — Panagiotis Kanavos, Jun 26 '19 at 11:06
What are you trying to do? How is this query used? You could remove the database name from the table name with a separate `USE` statement [like this answer](https://stackoverflow.com/a/13294236/134204). The code may be a bit ugly, but it's safe from SQL injection — Panagiotis Kanavos, Jun 26 '19 at 11:10

score 0 · Answer 1 · answered Jun 26 '19 at 10:35

0

String literals need to be enclosed in single quotes. So your query string composition should look something like:

'SELECT '''  + @quarterForDbName + ''' AS Quarter, ADDR_LINE_1, ADDR_LINE_2, ADDR_LINE_3, ADDR_LINE_4 FROM EDB_'+ @quarterForDbName +'.dbo.POSTAL_ADDRESS PA WHERE PA.EIRCODE = ''' + @EIRCODE + ''''

You already did that with @quarterForDbName, so you seem to know it in general.

But yeah, that splitting in different databases smells a little, as Panagiotis Kanavos already commented. So reworking that whole concept of yours probably is the real solution.

answered Jun 26 '19 at 10:35

sticky bit

36,626
12
31
42

The problem was *caused* by string concatenation. Quoting won't fix anything. The safe way is to use parameterized queries. In this case, instread of `WHERE PA.EIRCODE = ''' + @EIRCODE + '''` a simple `WHERE PA.EIRCODE = @EIRCODE` will work just fine. The same with `SELECT ''' + @quarterForDbName + ''' `, the safe and easy way is `SELECT @quarterForDbName AS Quarter`. The real problem is that `@quarterForDbName` is used as a database name. – Panagiotis Kanavos Jun 26 '19 at 11:03

score 0 · Answer 2 · answered Jun 26 '19 at 11:38

Try this

DECLARE @query VARCHAR(MAX)
DECLARE @quarterForDbName VARCHAR(MAX)
DECLARE @EIRCODE VARCHAR(MAX)
SET @EIRCODE='1'
SET @quarterForDbName='1'


SET @query='SELECT '''  + @quarterForDbName + ''' AS Quarter, ADDR_LINE_1, ADDR_LINE_2, ADDR_LINE_3, ADDR_LINE_4 FROM EDB_'
+ @quarterForDbName +'.dbo.POSTAL_ADDRESS PA WHERE PA.EIRCODE = ' + @EIRCODE 

 SELECT @query

How to correctly pass string as a parameter

2 Answers2