CODEWITHSUNDEEP
sqlsql-server
Jess
Jess

Reputation: 25

WHERE clause equal to variable

Here is my SQL Statement that gives 0 results. When I execute in SQL Server Management Studio, I fill in the variable @SessionName value as My Session 1

SELECT DISTINCT SessionFileName, SessionPath
FROM [vecAttendeeSessionFiles]
WHERE Session_Name = @SessionName

If I change it to 'My Session 1', it works with 5 results:

SELECT DISTINCT SessionFileName, SessionPath
FROM [vecAttendeeSessionFiles]
WHERE Session_Name = 'My Session 1'

What is the correct verbiage for this variable to work in the WHERE statement?

Here is my stored procedure:

USE [VEC]
GO
/****** Object:  StoredProcedure [dbo].[getSessionFiles]    Script Date: 11/30/2020 4:22:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      
-- Create date: 
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[getSessionFiles]
@SessionName nvarchar
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT DISTINCT SessionFileName, SessionPath
    FROM [vecAttendeeSessionFiles]
    WHERE Session_Name = @SessionName
END

Upvotes: 0

Views: 137

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Never use character types without a length! There is a default. In your context, the default is 1. Instead of:

ALTER PROCEDURE [dbo].[getSessionFiles]
@SessionName nvarchar(1)
AS

You probably want something like:

ALTER PROCEDURE [dbo].[getSessionFiles] (
    @SessionName nvarchar(255)
) AS

Upvotes: 1

Related Questions