SQLTeam.com | Weblogs | Forums

Declare Parameter not Working


#1

Hi All,
When I pass the value directly into my WHERE clause its working,
when the same value pass through declare statement it doesn't work appreciate your help

Declare 
 @ISB nvarchar(max) = 201712061954582812;

 select	
 IsNull(tblInvoiceHeader.InvoiceTicketText,'None') as PrintGroup
 	,count(*) as Invoices,
 .................
 	where	tblinvoiceHeader.InvoiceType = 'G'
	--AND tblInvoiceHeader.InvoiceSageBatch = '201712061954582812'
 	AND tblInvoiceHeader.InvoiceSageBatch = '@ISB'

Appreciate your help

Kind regards,
Farhan


#2

You need quotes around the "number" part

Declare @ISB nvarchar(max) = '201712061954582812';

Do NOT use nvarchar(MAX) for this (unless you actually want to allow a @Parameter in excess of 4,000 characters. Huge waste of resources if not needed, SQL will pre-allocate memory on the assumption of the size that the object that the @Paramneter MIGHT hold.

and do NOT have quotes around the test

that should be

AND tblInvoiceHeader.InvoiceSageBatch = @ISB

#3

Sorry I did try with that as well , also change the type to varchar(max) doesn't work :frowning:

Already tried the following
varchar(100)
varchar(50)
int(50
nvarchar(50)

result is null


#4

Its the MAX part that you should avoid. Size the @Parameter accordingly.

Sorry, pressed SAVE before I'd finished typing, I've changed my earlier answer after you read it to add a second point.


#5

oh my god , how stupid I'm ... thanks a lot , the quotes were making misstate when I passing the variable
Thanks a lot , I think its Monday...
Thanks again Kristen


#6

Hehehe ... no problem, second-pair-of-eyes always makes spotting that easy.

I've got a couple of other suggestions:

Don't use "tbl" prefix. When you decide to massively revamp the tblInvoiceHeader table, and for backward compatibility you need a "version 1" table, the straightforward choice will be to create tblInvoiceHeader_V2, with all the column name changes, data type alterations, and you might also sequence the columns in a different order, etc. and so on.

If it was me I would RENAME the existing table tblInvoiceHeader to tblInvoiceHeader_V2, make all the changes, and then so that all my old, legacy, code would work I would create a VIEW with the old tblInvoiceHeader name (and all the original Column namaes, in the original order, etc. etc.) and Hey Presto all my old, legacy, code would still work.

But now that tblInvoiceHeader is a VIEW, and no longer a table, the "tbl" prefix is confusing and will lead to programming errors.

Just call your tables InvoiceHeader

I recommend that you ALIAS your table, rather than using the name in full:

select	
 IsNull(I.InvoiceTicketText,'None') as PrintGroup
 	,count(*) as Invoices,
FROM tblInvoiceHeader AS I
 	where	I.InvoiceType = 'G'
 	AND I.InvoiceSageBatch = @ISB

when you have to self-reference a table, or for some other reason have a table appearing twice, or more, in your FROM / JOIN statements, you will have to alias it, and having the name in full will just mean that you have double-standards between "simple code" and "complex code".

Better to start now with simple ALIAS names for tables.