Parameters within parameter in sql query

Transact-SQL
1

Hello SQL Expert,

Is that true that the first stored procedure is the most best practice to do and fast, compare with the second one?

Stored Procedure # 1:

CREATE PROCEDURE TestSQLQuery
@Param VARCHAR(10)

Declare @Param1 VARCHAR(10) = @Param1

SELECT Field1, Field2, Field3, Field4, Field5
FROM TBLA
WHERE Field1 = @Param1

Stored Procedure # 2:

CREATE PROCEDURE TestSQLQuery
@Param VARCHAR(10)

SELECT Field1, Field2, Field3, Field4, Field5
FROM TBLA
WHERE Field1 = @Param

Thank you guys

2

It depends on your structure, on how your data is spread ...

Please read this http://www.benjaminnevarez.com/2010/06/how-optimize-for-unknown-works/

when you use variable like in sp1, this is equivalent to optimize for unknown

1 Like
3

Thank you stepson. Very useful info.

4

My pleasure. I hope it's more clear now.
Credit for the author , to Benjamin N :wink:

5

Use format 2. If performance continues to be a problem, do this:

CREATE PROCEDURE TestSQLQuery
@Param VARCHAR(10)

SELECT Field1, Field2, Field3, Field4, Field5
FROM TBLA
WHERE Field1 = @Param
OPTION (RECOMPILE)