Using Variables in SQL Queries for Scripts and SQLServer Stored Procedures | Essential SQL

If you want to make your queries more flexible, then watch this video to learn how you can add variables to your SQL Server scripts and stored procedures.

Check out our channel at @EssentialSQL to learn even more about SQL Server.

Here are the scripts for those interested:

--Simple Select
select *
from Person.Person
Where LastName like 'Ral%'

--Select with variable in Query
declare @LastNamePattern as varchar(40);
set @LastNamePattern = 'Ral%'

select *
from Person.Person
Where LastName like @LastNamePattern

--Make it a stored procedure and run it!
AS
begin

select *
from Person.Person
Where LastName like @LastNamePattern

end

exec dbo.PersonSearchLastName 'Kal%'

[Twiztermate]

This is a definition breaker for me. teachings were on point and also informative.
Thanks a whole lot for this. I hope to see more videos.

[alisonhenley]

Really great, I can totally see the practicality now thanks to your video.

[hellmutmatheus]

Thats a very nice tutorial video, straight to the point! really thank you!

[Termoil]

Thansk Kris. I learn something every time from your excellent videos

[mojojojo]

That was so helpful, great channel out there

[lmeza]

I just bought your udemy course on stored procedures but couldn't declare a variable like you did because I wasn't using a joined table in the credit card example (I was not just typing your example but made an easier table) then I found the solution here??? Well I couldn't declare the output yet.

ALTER procedure [dbo].[GetCC]

@first varchar(10),
@last varchar(10)

AS
BEGIN

(select creditcard from credit_card where firstname like @first and lastname like @last)

END


What I was trying to do is to declare @CC in order to mask the digits with **** but I have not been able to set that yet (scalar variable error message).

[KiranSingh-bkuz]

Hello @Kris, Please make a video why's and where we use outer apply and cross apply within subquery and correlated subquery, why's we need for this.

[CHSKnight]

What IDE are you using here? Is it open source? I like it

[Helloimtheshiieet]

not a bad explanation / walkthrough but I wasn't a sql dev I would not understand what the difference is between stored proc vs declare/set should have spent 10 min after explaining ''why' at the end

[CHSKnight]

Hi Kris, so it is not neccessary to add "SET NOCOUNT ON;" after AS BEGIN when you create a stored procedure?

I normally do it. SET NOCOUNT ON at the top and SET NOCOUNT OFF at the bottom before I end the procedure

[jamesr]

But how do I simply pass a variable into the WHERE clause? e.g., await db.query(`INSERT INTO agents(token) VALUES($1) WHERE id=${newAgentID} RETURNING *;`, [token])