Help in dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help in dynamic SQL

Author

Topic

gvmk27
Starting Member

44 Posts

Posted - 2014-10-06 : 15:40:07

I was getting below error while try to execute the dynamic SQL

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@tblCalls".

Please help.
Mohan
_____________________________________________________
Declare @Status int
Declare @StartDate smalldatetime
Declare @EndDate smalldatetime
Declare @CenterID nvarchar(10)
Declare @Center nvarchar(100)
Declare @ParentName nvarchar(100)
Declare @StudentName nvarchar(100)
Declare @StudentID char(14)
Declare @Phone nvarchar(15)
Declare @Email nvarchar(15)
Declare @StreetAddress nvarchar(15)

Declare @SqlCall nvarchar(1000)
Declare @SqlAppointment nvarchar(1000)
Declare @Sql nvarchar(3000)
Declare @Where nvarchar(200)

SET @CenterID = '00341270'
--'16001134'
SET @Center = NULL -- 'tean'
SET @Email = NULL -- '@gmail.com'
SET @Phone = NULL --'222-222-2222'
SET @StartDate = NULL -- '01/1/2014'
SET @EndDate = NULL -- '08/01/2014'
SET @StreetAddress = NULL
SET @ParentName = NULL
SET @Status = 1

IF (@Status = 1)
BEGIN

DECLARE @tblCalls table(
ID int,
CallDate smalldatetime,
AppointmentTime smalldatetime,
CenterID char(10),
FirstName nvarchar(50),
LastName nvarchar(50),
PhoneNumber nvarchar(25),
Email nvarchar(50),
Address1 nvarchar(50),
Address2 nvarchar(50),
City nvarchar(50),
ZipCode nvarchar(50),
Notes nvarchar(3000),
StudentName nvarchar(10),
Gender char(10),
Age tinyint,
Grade varchar(3))

SET @SqlCall =
'INSERT INTO @tblCalls (ID,
CallDate,
AppointmentTime,
CenterID,
FirstName,
LastName,
PhoneNumber,
Email,
Address1,
Notes,
StudentName,
Gender,
Age,
Grade)
SELECT Cl.CallID as ID, CAST(Cl.StartTime AS DATE) as CallDate, GETDATE() As AppointmentTime,
CASE
WHEN Cl.CenterIDOnStartUp = 1 THEN Cl.CenterID
ELSE ''N/A''
END
[Center ID], FirstName, LastName, PhoneNumber, Email,
''N/A'' As Address, ''N/A'' As Notes,
CS.StudentName, CS.Gender, CS.Age, CS.Grade
FROM [Call] Cl
LEFT JOIN Center C ON C.CenterID = Cl.CenterID
LEFT JOIN (
SELECT CallID, StudentName, Age, Grade, Gender, SubjectID, StudentTypeID FROM CallStudent
) CS ON CS.CallID = Cl.CallID WHERE 1 = 1'

IF Len(@CenterID) > 0
BEGIN
SET @Where = ' AND Cl.CenterID = ' + @CenterID
END

IF Len(@Center) > 0
BEGIN
SET @Where = @Where + ' AND C.Center like ''%' + CAST(@Center as nvarchar(50)) + '%'''
END

IF Len(@Email) > 0
BEGIN
SET @Where = @Where + ' AND Cl.Email like ''%' + CAST(@Email as nvarchar(50)) + '%'''
END

IF LEN(@Where) > 0
BEGIN
SET @SqlCall = (@SqlCall + @Where)
--print (@Sql + @Where)
END
--ELSE
-- BEGIN
-- EXEC (@SqlCall)
-- END

set @SqlAppointment = 'SELECT A.AppointmentID as ID,
CAST(A.CreateDate AS DATE) as CallDate,
CONVERT(DATETIME, CONVERT(CHAR(8), A.AppointmentDate, 112) + '' '' + CONVERT(CHAR(8), A.StartTime, 108))
as AppointmentTime,
C.CenterID, A.FirstName, A.LastName, A.PhoneNumber,
A.Email,
A.Address1 + A.Address2 + A.City + A.ZipCode as [Address],
A.Notes,
AST.StudentName, AST.Gender, AST.Age, AST.Grade
FROM Appointment A
INNER JOIN Center C ON C.CenterGUID = A.CenterGUID
LEFT JOIN (
SELECT AppointmentID, StudentName, Age, Grade, Gender, SubjectID, StudentTypeID FROM AppointmentStudent
) AST ON AST.AppointmentID = A.AppointmentID
--AND Getdate() <= AppointmentDate
AND AppointmentDate >= DATEADD(MONTH, -2, GETDATE())'

set @Where = ' '
IF Len(@CenterID) > 0
BEGIN
SET @Where = ' AND C.CenterID = ' + @CenterID
END

IF Len(@Center) > 0
BEGIN
SET @Where = @Where + ' AND C.Center like ''%' + CAST(@Center as nvarchar(50)) + '%'''
END

IF Len(@Email) > 0
BEGIN
SET @Where = @Where + ' AND A.Email like ''%' + CAST(@Email as nvarchar(50)) + '%'''
END

IF LEN(@Where) > 0
BEGIN
SET @SqlAppointment = (@SqlAppointment + @Where)
--print (@Sql + @Where)
END

EXEC (@SqlCall + ' UNION ' + @SqlAppointment)
--PRINT (@SqlCall + ' UNION ' + @SqlAppointment)
SELECT * FROM @tblCalls

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-06 : 15:43:26

Dynamic SQL executes in a different session than your code, so the @tblCalls table variable isn't available in that other session.

I don't think you even need dynamic SQL here, but I can't make heads or tails of your code.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gvmk27
Starting Member

44 Posts

Posted - 2014-10-06 : 15:49:32

Hi Tara

Thanks for you quick reply. I was trying to implement search functionality.

I need to search across Calls & Appointment tables. Most of the appointments are made out of calls, but there are few appointments made with out calls.

User can search call or appointments with any combination of below parameters. StartDate, endDate, FirstName, LastName, PhoneNumber, Email etc.

Without dynamic SQL, how can we achieve this ? Let me know if you need more info.

Regards
MOhan

quote:
Originally posted by tkizer

Dynamic SQL executes in a different session than your code, so the @tblCalls table variable isn't available in that other session.

I don't think you even need dynamic SQL here, but I can't make heads or tails of your code.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-06 : 16:03:48

You can use ISNULL in the WHERE clause for each. Research "dynamic where clause" to see some examples.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gvmk27
Starting Member

44 Posts

Posted - 2014-10-06 : 18:03:06

Thanks for your help. I modified the SQL as below. I will appreciate if you can let me know further improvement on this. Thanks for your help.

Declare @Status int
Declare @StartDate smalldatetime
Declare @EndDate smalldatetime
Declare @CenterID nvarchar(10)
Declare @Center nvarchar(100)
Declare @ParentName nvarchar(100)
Declare @StudentName nvarchar(100)
Declare @StudentID char(14)
Declare @Phone nvarchar(50)
Declare @Email nvarchar(15)
Declare @StreetAddress nvarchar(15)

Declare @SqlCall nvarchar(1000)
Declare @SqlAppointment nvarchar(1000)
Declare @Sql nvarchar(3000)
Declare @Where nvarchar(200)

SET @CenterID = NULL
--'16001134'
SET @Center = 'hack'
SET @Email = NULL
SET @Phone = NULL --'222-222-2222'
SET @StartDate = NULL -- '01/1/2014'
SET @EndDate = NULL -- '08/01/2014'
SET @StreetAddress = NULL
SET @ParentName = NULL
SET @Status = 2

IF @Email IS NOT NULL
BEGIN
SET @Email = '%' + @Email + '%'
END

IF @Center IS NOT NULL
BEGIN
SET @Center = '%' + @Center + '%'
END

IF (@Status = 1)
BEGIN

DECLARE @tblCalls table(
ID int,
CallDate smalldatetime,
AppointmentTime smalldatetime,
CenterID char(10),
FirstName nvarchar(100),
LastName nvarchar(100),
PhoneNumber nvarchar(50),
Email nvarchar(50),
Address1 nvarchar(100),
Address2 nvarchar(100),
City nvarchar(100),
ZipCode nvarchar(100),
Notes nvarchar(3000),
StudentName nvarchar(100),
Gender char(10),
Age tinyint,
Grade varchar(3))

INSERT INTO @tblCalls (ID,
CallDate,
AppointmentTime,
CenterID,
FirstName,
LastName,
PhoneNumber,
Email,
Address1,
Notes,
StudentName,
Gender,
Age,
Grade)

SELECT Cl.CallID as ID, CAST(Cl.StartTime AS DATE) as CallDate, GETDATE() As AppointmentTime,
CASE
WHEN Cl.CenterIDOnStartUp = 1 THEN Cl.CenterID
ELSE 'N/A'
END
[Center ID], FirstName, LastName, PhoneNumber, Email,
'N/A' As Address, 'N/A' As Notes,
CS.StudentName, CS.Gender, CS.Age, CS.Grade
FROM [Call] Cl
LEFT JOIN Center C ON C.CenterID = Cl.CenterID
LEFT JOIN (
SELECT CallID, StudentName, Age, Grade, Gender, SubjectID, StudentTypeID FROM CallStudent
) CS ON CS.CallID = Cl.CallID WHERE 1 = 1

AND (@CenterID IS NULL OR Cl.CenterID Like @CenterID)
AND (@Center IS NULL OR C.Center Like @Center )
AND (@Email IS NULL OR Cl.Email Like @Email )

-- --print (@SQL)
--SELECT * FROM @tblCalls

INSERT INTO @tblCalls (ID,
CallDate,
AppointmentTime,
CenterID,
FirstName,
LastName,
PhoneNumber,
Email,
Address1,
Notes,
StudentName,
Gender,
Age,
Grade)

SELECT A.AppointmentID as ID,
CAST(A.CreateDate AS DATE) as CallDate,
--CONVERT(DATETIME, CONVERT(CHAR(8), A.AppointmentDate, 112)+ ' ' + CONVERT(CHAR(8), A.StartTime, 108))
A.AppointmentDate
as AppointmentTime,
C.CenterID, A.FirstName, A.LastName, A.PhoneNumber,
A.Email,
A.Address1 + A.Address2 + A.City + A.ZipCode as [Address],
A.Notes,
AST.StudentName, AST.Gender, AST.Age, AST.Grade
FROM Appointment A
INNER JOIN Center C ON C.CenterGUID = A.CenterGUID
LEFT JOIN (
SELECT AppointmentID, StudentName, Age, Grade, Gender, SubjectID, StudentTypeID FROM AppointmentStudent
) AST ON AST.AppointmentID = A.AppointmentID
AND A.AppointmentDate >= DATEADD(MONTH, -2, GETDATE())

AND (@CenterID IS NULL OR C.CenterID Like @CenterID)
AND (@Center IS NULL OR C.Center Like @Center )
AND (@Email IS NULL OR A.Email Like @Email )

SELECT * FROM @tblCalls

END
ELSE
IF ((@Status = 2) OR (@Status = 3) OR (@Status = 4) OR (@Status = 5))
BEGIN
DECLARE @CallTypeID char(2)
DECLARE @CallEndTypeID char(2)
--SET @CallTypeID = 0
--SET @CallEndTypeID = 0

SELECT @CallTypeID =
CASE @Status
WHEN 2 THEN 1
WHEN 3 THEN 2
END

SELECT @CallEndTypeID =
CASE @Status
WHEN 4 THEN 1
WHEN 5 THEN 2
END

SELECT Cl.CallID as ID, CAST(Cl.StartTime AS DATE) as CallDate, GETDATE() As AppointmentTime,
CASE
WHEN Cl.CenterIDOnStartUp = 1 THEN Cl.CenterID
ELSE 'N/A'
END
[Center ID], FirstName, LastName, PhoneNumber, Email,
'N/A' As Address, 'N/A' As Notes,
CS.StudentName, CS.Gender, CS.Age, CS.Grade
FROM [Call] Cl
LEFT JOIN dbo.Center C ON C.CenterID = Cl.CenterID
LEFT JOIN (
SELECT CallID, StudentName, Age, Grade, Gender, SubjectID, StudentTypeID FROM CallStudent
) CS ON CS.CallID = Cl.CallID WHERE 1 = 1
AND Cl.CreateDate >= DATEADD(MONTH, -2, GETDATE())

AND (@CallTypeID IS NULL OR Cl.CallTypeID = 1)
AND (@CallEndTypeID IS NULL OR Cl.CallEndType = @CallEndTypeID)
AND (@CenterID IS NULL OR Cl.CenterID = @CenterID)
AND (@Center IS NULL OR C.Center like @Center)
AND (@Email IS NULL OR Cl.Email like @Email)

END
ELSE
IF (@Status = 6)
BEGIN
-- Search from Booked Appointments
SELECT
A.AppointmentID AS ID,
A.AppointmentDate, C.CenterID, C.Center, A.FirstName + A.LastName as ParentName, A.PhoneNumber, A.Email,
A.Address1 + A.Address2 + A.City as Address, A.Notes
FROM Appointment A
INNER JOIN dbo.Center C ON C.CenterGUID = A.CenterGUID
WHERE 1 = 1

AND (@CenterID IS NULL OR C.CenterID = @CenterID)
AND (@Center IS NULL OR C.Center = @Center)
AND (@ParentName IS NULL OR A.FirstName = @ParentName)
AND (@StreetAddress IS NULL OR A.Address1 = @StreetAddress)
AND (@Email IS NULL OR A.Email = @Email)
AND (@Phone IS NULL OR A.PhoneNumber = @Phone)
--AND (((@StartDate IS NULL) AND (@EndDate IS NULL)) OR A.AppointmentDate > DATEADD(year,-1,GETDATE()))

--IF ((@StartDate IS NULL) AND (@EndDate IS NULL))
-- BEGIN
-- AND A.AppointmentDate > DATEADD(year,-1,GETDATE())
-- END
--ELSE
--IF ((@StartDate IS NULL) AND (@EndDate IS NOT NULL))
-- BEGIN
-- SET @Where = @Where + ' AND A.AppointmentDate > DATEADD(year,-1,GETDATE())'
-- END
--ELSE
--IF ((@StartDate IS NOT NULL) AND (@EndDate IS NULL))
-- BEGIN
-- SET @Where = @Where + ' AND A.AppointmentDate > DATEADD(year,-1,GETDATE())'
-- END
--ELSE
-- BEGIN
-- --PRINT @Where
-- SET @Where = @Where + ' AND A.AppointmentDate BETWEEN ''' + CONVERT(VARCHAR(12),@StartDate, 101) +''' AND '''+ CONVERT(VARCHAR(12),@EndDate, 101)+ ''''
-- END

END

quote:
Originally posted by tkizer

You can use ISNULL in the WHERE clause for each. Research "dynamic where clause" to see some examples.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-06 : 18:37:58

Not "IS NULL", but ISNULL function. http://www.codeproject.com/Articles/21234/Implementing-Dynamic-WHERE-Clause-in-Static-SQL

Or use COALESCE.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-07 : 07:48:54

[code]-- Mimic user supplied parameter values
DECLARE @Status INT = 2,
@StartDate SMALLDATETIME = '20140101',
@EndDate SMALLDATETIME = '20140801',
@CenterID NVARCHAR(10) = N'16001134',
@Center nvarchar(100) = N'hack',
@ParentName NVARCHAR(100),
@StudentName NVARCHAR(100),
@StudentID NCHAR(14),
@Phone NVARCHAR(50) = N'222-222-2222',
@eMail NVARCHAR(15),
@StreetAddress NVARCHAR(15);

-- Make sure eMail and Center are wildcarded
SELECT @eMail = CASE WHEN @eMail >= N'' THEN N'%' + @eMail + N'%' ELSE NULL END,
@Center = CASE WHEN @Center >= N'' THEN N'%' + @Center + N'%' ELSE NULL END;

IF @Status = 1
SELECT ca.CallID AS ID,
CAST(ca.StartTime AS DATE) AS CallDate,
GETDATE() AS AppointmentTime,
CASE
WHEN ca.CenterIDOnStartUp = 1 THEN ca.CenterID
ELSE 'N/A'
END AS [Center ID],
ca.FirstName,
ca.LastName,
ca.PhoneNumber,
ca.eMail,
N'N/A' AS [Address],
N'N/A' As Notes,
s.StudentName,
s.Gender,
s.Age,
s.Grade
FROM dbo.[Call] AS ca
INNER JOIN dbo.CallStudent AS s ON s.CallID = ca.CallID
INNER JOIN dbo.Center AS c ON c.CenterID = ca.CenterID
AND (@Center IS NULL OR c.Center LIKE @Center)
WHERE (@Email IS NULL OR ca.eMail LIKE @Email)

UNION ALL

SELECT a.AppointmentID AS ID,
CAST(A.CreateDate AS DATE) AS CallDate,
a.AppointmentDate AS AppointmentTime,
c.CenterID,
a.FirstName,
a.LastName,
a.PhoneNumber,
a.eMail,
a.Address1 + a.Address2 + a.City + a.ZipCode AS [Address],
a.Notes,
AST.StudentName,
AST.Gender,
AST.Age,
AST.Grade
FROM dbo.Appointment AS a
INNER JOIN dbo.Center AS c ON c.CenterGUID = a.CenterGUID
AND (@Center IS NULL OR c.Center LIKE @Center)
AND (@CenterID IS NULL OR c.CenterID LIKE @CenterID)
LEFT JOIN dbo.AppointmentStudent AS s ON s.AppointmentID = a.AppointmentID
WHERE a.AppointmentDate >= DATEADD(MONTH, -2, GETDATE())
AND (@Email IS NULL OR a.Email LIKE @Email)
ELSE IF @Status IN (2, 3, 4, 5)
SELECT Cl.CallID AS ID,
CAST(Cl.StartTime AS DATE) AS CallDate,
GETDATE() As AppointmentTime,
CASE
WHEN Cl.CenterIDOnStartUp = 1 THEN Cl.CenterID
ELSE 'N/A'
END AS [Center ID],
FirstName,
LastName,
PhoneNumber,
eMail,
'N/A' AS Address,
'N/A' As Notes,
CS.StudentName,
CS.Gender,
CS.Age,
CS.Grade
FROM dbo.[Call] AS Cl
LEFT JOIN dbo.Center AS C ON C.CenterID = Cl.CenterID
LEFT JOIN dbo.CallStudent AS cs ON cs.CallID = cl.CallID
WHERE Cl.CreateDate >= DATEADD(MONTH, -2, GETDATE())
AND (@CallTypeID IS NULL OR Cl.CallTypeID = 1)
AND (@CallEndTypeID IS NULL OR Cl.CallEndType = CASE @Status WHEN 4 THEN 1 WHEN 5 THEN 2 END)
AND (@CenterID IS NULL OR Cl.CenterID = @CenterID)
AND (@Center IS NULL OR C.Center like @Center)
AND (@Email IS NULL OR Cl.Email like @Email)
ELSE IF @Status = 6
SELECT A.AppointmentID AS ID,
A.AppointmentDate,
C.CenterID,
C.Center,
A.FirstName + A.LastName AS ParentName,
A.PhoneNumber,
A.Email,
A.Address1 + A.Address2 + A.City AS Address,
A.Notes
FROM dbo.Appointment A
INNER JOIN dbo.Center C ON C.CenterGUID = A.CenterGUID
AND (@CenterID IS NULL OR C.CenterID = @CenterID)
AND (@Center IS NULL OR C.Center = @Center)
WHERE (@ParentName IS NULL OR A.FirstName = @ParentName)
AND (@StreetAddress IS NULL OR A.Address1 = @StreetAddress)
AND (@Email IS NULL OR A.Email = @Email)
AND (@Phone IS NULL OR A.PhoneNumber = @Phone);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

gvmk27
Starting Member

44 Posts

Posted - 2014-10-07 : 10:55:52

Thanks a lot SwePeso & Tara for you guidance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources