Dynamic Order By Clause

I'm trying to dynamically determine the Order By clause, like I would in VB,
but I can't figure out the syntax. This is my SP. Thanks
CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
@.mForumID int,
@.mSearchValue varchar(100),
@.mOrderBy varchar(20)
AS
Declare @.OrderByField as varchar(20)
Select @.OrderByField =
CASE
WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
WHEN @.mOrderBy = "Author" THEN ' users.UName'
WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
ELSE 'MB_Posts.ID!'
END
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY @.OrderByField
GOHi,
See the below URL:-
http://www.sommarskog.se/dynamic_sql.html#Order_by
Thanks
Hari
SQL Server MVP
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F1BF6BDD-C08C-4EBA-993B-0AF6080516C9@.microsoft.com...
> I'm trying to dynamically determine the Order By clause, like I would in
> VB,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>|||Order By clause requires a literal string. You might want to take a look at
Erland's article for some info/work around.
http://www.sommarskog.se/dynamic_sql.html#Order_by
-oj
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F1BF6BDD-C08C-4EBA-993B-0AF6080516C9@.microsoft.com...
> I'm trying to dynamically determine the Order By clause, like I would in
> VB,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>|||Thanks for pointing me in the right direction. I have different data types,
so I had to use the second format. The only problem is that it won't sort i
n
DESC order. This is what is looks like now. Any suggestions?
CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
@.mForumID int,
@.mSearchValue varchar(100) =null,
@.mOrderBy varchar(20)=null
AS
IF RIGHT(@.mOrderBy,4)='Desc'
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY CASE @.mOrderBy WHEN NULL THEN MB_Posts.ID ELSE NULL END,
CASE @.mOrderBy WHEN 'LastPostDesc' THEN MB_Posts.Last_Post
ELSE NULL END,
CASE @.mOrderBy WHEN 'AuthorDesc' THEN users.UName ELSE
NULL END,
CASE @.mOrderBy WHEN 'RepliesDesc' THEN MB_Posts.Replies
ELSE NULL END,
CASE @.mOrderBy WHEN 'TopicDesc' THEN MB_Posts.Subject ELSE
NULL END
DESC
ELSE
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY CASE @.mOrderBy WHEN NULL THEN MB_Posts.ID ELSE NULL END,
CASE @.mOrderBy WHEN 'LastPost' THEN MB_Posts.Last_Post
ELSE NULL END,
CASE @.mOrderBy WHEN 'Author' THEN users.UName ELSE NULL END,
CASE @.mOrderBy WHEN 'Replies' THEN MB_Posts.Replies ELSE
NULL END,
CASE @.mOrderBy WHEN 'Topic' THEN MB_Posts.Subject ELSE
NULL END
ASC
"Phill" wrote:

> I'm trying to dynamically determine the Order By clause, like I would in V
B,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.
ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Pos
t'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Repli
es'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subjec
t'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>

Dynamic filter using a UDF

In BOL it says that you can use a UDF in a dynamic filter, passing in one of
the system UDFs. However, when I try this I get a syntax error, e.g.
SELECT * FROM [dbo].[SiteData]
WHERE SiteId IN (SELECT Value FROM dbo.Split(HOST_NAME(), ','))
where dbo.Split is a UDF that splits a list of values into a table
It doesn't seem to like the brackets after HOST_NAME, but the example shown
in BOL is MYUDF(HOSTNAME())
Any ideas what I've got wrong with the syntax?
TIA
Paul
Paul,
the only way I've been able to resolve a system function to multiple values
is to use a linking table and replicate it as well. Have a look at the
example in the merge section of www.replicationanswers.com where there is a
related example.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
Thanks for that, I can make it work in my scenario - BTW you also quote the
MYUDF(HOST_NAME()) solution as well further down the page.
Regards
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%236k7vXQxEHA.3612@.tk2msftngp13.phx.gbl...
> Paul,
> the only way I've been able to resolve a system function to multiple
> values
> is to use a linking table and replicate it as well. Have a look at the
> example in the merge section of www.replicationanswers.com where there is
> a
> related example.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
I'll edit the page to make clearer, but I've only used
the MYUDF(HOST_NAME()) solution for scalars.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'll need to check this out in SQL 2005 beta to see if it's fixed or if
anyone can give a reason why ...
SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
dbo.Split('1, 2', ','))
compile (and runs), but
SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
dbo.Split(HOST_NAME(), ','))
does not, whereas
SELECT * FROM [dbo].[SiteData] WHERE SiteId = MyUdf(HOST_NAME())
compiles again.
It's nothing to do with replication per se, but how the complier is parsing
the statements.
Thanks
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4d0d01c4c575$382daee0$a301280a@.phx.gbl...
> Paul,
> I'll edit the page to make clearer, but I've only used
> the MYUDF(HOST_NAME()) solution for scalars.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||why don't you put the hostname within your function as opposed to supplying
it as an argument.
"Paul Hatcher" <phatcher@.nospam.cix.co.uk> wrote in message
news:OHJ9%238YxEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I'll need to check this out in SQL 2005 beta to see if it's fixed or if
> anyone can give a reason why ...
> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
> dbo.Split('1, 2', ','))
> compile (and runs), but
> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
> dbo.Split(HOST_NAME(), ','))
> does not, whereas
> SELECT * FROM [dbo].[SiteData] WHERE SiteId = MyUdf(HOST_NAME())
> compiles again.
> It's nothing to do with replication per se, but how the complier is
> parsing the statements.
> Thanks
> Paul
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:4d0d01c4c575$382daee0$a301280a@.phx.gbl...
>
|||Because it says in BOL that you can't use dynamic snapshots if neither of
HOST_NAME/SUSER_NAME() functions are not specified in the row filter. The
scenario is a largish central database with a bunch of subsidiary offices,
some of which are on dial-up, so I'm trying everything to minimise the
amount of time taken to do replication and the amount of data on the wire.
I'll try it anyway and see what happens; the other point is that I've come
across Transact-SQL oddities like this before, and it's a good idea to get
rid of them if at all possible.
Thanks
Paul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OX8lbLaxEHA.1400@.TK2MSFTNGP11.phx.gbl...
> why don't you put the hostname within your function as opposed to
> supplying it as an argument.
> "Paul Hatcher" <phatcher@.nospam.cix.co.uk> wrote in message
> news:OHJ9%238YxEHA.2624@.TK2MSFTNGP11.phx.gbl...
>

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
Jamie
Hi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie
|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
Regards,
Jamie
"Uri Dimant" wrote:

> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pas
s
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:

> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
>
>

Dynamic date value required for function / view

Is there a method to send getdate() to a function without the function
returning an error: (functions is called from a view)
Incorrect syntax near '('
example:
Select * from myfunction(getdate())
create function myfunction (@.Today datetime)
returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
Begin
--do stuff
return
End
--
Regards,
JamieHi
declare @.dt datetime
set @.dt=getdate()
Select * from myfunction(@.dt)
create function myfunction (@.Today datetime)
RETURNS TABLE
as
RETURN (SELECT
*
FROM
Orders
WHERE
orderdate < = @.Today)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> Is there a method to send getdate() to a function without the function
> returning an error: (functions is called from a view)
> Incorrect syntax near '('
> example:
> Select * from myfunction(getdate())
> create function myfunction (@.Today datetime)
> returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> Begin
> --do stuff
> return
> End
>
> --
> Regards,
> Jamie|||Thanks Uri, but... Ah, if only it were that simple. I gave up on it and
chose an alternate route. The requirement was to see only a specific "dates
between..." from a view. The view can call a function, but a view can't pass
a variable.
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> declare @.dt datetime
> set @.dt=getdate()
> Select * from myfunction(@.dt)
> create function myfunction (@.Today datetime)
> RETURNS TABLE
> as
> RETURN (SELECT
> *
> FROM
> Orders
> WHERE
> orderdate < = @.Today)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:BD17A036-4B83-41AC-9C6F-722213242877@.microsoft.com...
> > Is there a method to send getdate() to a function without the function
> > returning an error: (functions is called from a view)
> > Incorrect syntax near '('
> >
> > example:
> > Select * from myfunction(getdate())
> >
> > create function myfunction (@.Today datetime)
> > returns @.mytbl(id int identity(1,1) null, myfield varchar(10) null)
> > Begin
> > --do stuff
> > return
> > End
> >
> >
> >
> > --
> > Regards,
> > Jamie
>
>

Dy

I have to get a count of records using dynamic sql.
I have the following and I'm getting errors (Syntax error converting the
varchar value 'SELECT ' to a column of data type int.):
[Code]
DECLARE @.sCalldate varchar(10) , @.sAgentId varchar(10), @.sProject varchar(10
)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.sAgentid ='0'
SELECT @.sProject = 'A'
SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = ' + @.sCallDate +
' AND ISNULL(sRawLogout, ''x'') = ''x'''
IF @.sAgentId <> '0'
BEGIN
SELECT @.SQL = @.SQL + ' AND sAgentId = @.sAgentId '
END
IF @.sProject<> '0'
BEGIN
SELECT @.SQL = @.SQL + ' AND sProject= @.sProject'
END
EXEC (@.SQL)
If I put the @.RC inside the quotes I get error: Must declare the variable
'@.RC'.
I tested by printing the statement and it prints properly just errors when
executing.
How can I make this work?
Thanks,
Ninel
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200507/1EXEC() has its own scope, so has no idea what @.RC is (nor does it "see"
sAgentID or @.sProject).
Try getting rid of the dynamic SQL altogether:
SELECT @.RC = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = @.sCallDate
AND COALESCE(sRawLogout, 'x') = 'x'
AND sAgentID = CASE @.sAgentID WHEN 'O' THEN sAgentID ELSE @.sAgentID
END
AND sProject = CASE @.sProject WHEN 'O' THEN sProject ELSE @.sProject
END
Or see http://www.aspfaq.com/2492
"ninel gorbunov via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:51A8B62C943CC@.webservertalk.com...
> I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting the
> varchar value 'SELECT ' to a column of data type int.):
> [Code]
> DECLARE @.sCalldate varchar(10) , @.sAgentId varchar(10), @.sProject
> varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sAgentid ='0'
> SELECT @.sProject = 'A'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> IF @.sAgentId <> '0'
> BEGIN
> SELECT @.SQL = @.SQL + ' AND sAgentId = @.sAgentId '
> END
> IF @.sProject<> '0'
> BEGIN
> SELECT @.SQL = @.SQL + ' AND sProject= @.sProject'
> END
> EXEC (@.SQL)
> If I put the @.RC inside the quotes I get error: Must declare the variable
> '@.RC'.
> I tested by printing the statement and it prints properly just errors when
> executing.
> How can I make this work?
> Thanks,
> Ninel
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200507/1|||I have a similar problem.
USE MyDataBase
GO
if exists
(select * from sysobjects where id =
object_id(N'[dbo].[sp_MTV_Diskalert]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MTV_Diskalert]
GO
create procedure sp_MTV_diskalert (
@.LowerLimit INT
, @.LinkedServerName SYSNAME
, @.NumberOfEmailsToSend INT
, @.EmailAddress1 NVARCHAR(30)
, @.EmailAddress2 NVARCHAR(30))
AS
--
BEGIN
--
SET XACT_ABORT ON
SET NOCOUNT ON
--
DECLARE @.RCPT VARCHAR(500)
, @.LIMIT INT
, @.SQLSTRING NVARCHAR(4000)
, @.STRING2 NVARCHAR(4000)
, @.EMAIL NVARCHAR(4000)
, @.ParmDefinition NVARCHAR(4000)
, @.ParmIn NVARCHAR(4000)
, @.ParmRet NVARCHAR(4000)
, @.MyCounter int
, @.TempEmailAddress NVARCHAR(30)
--
set @.MyCounter = 0
--
--The following stmt works
--
exec (select @.EmailAddress1 as name)
--
while ( @.MyCounter <> @.NumberOfEmailsToSend )
Begin
SET @.MyCounter = @.MyCounter + 1
SET @.TempEmailAddress = '@.EmailAddress'+ltrim(str(@.MyCounter))
SET @.SQLString = N'Select ' +@.TempEmailAddress+ ' as name'
-- print @.SQLstring
EXECUTE sp_executesql
@.SQLString
,@.params = N'@.email nvarchar(30) output'
,@.email=@.email output
END
END
go
EXEC Sealedair.dbo.sp_SAC_diskalert
250,DUNSQL02,1,'Michael.varriale@.sealedair.com','2','3','4','5','6','7','8',
'9','10'
Must declare the variable '@.EmailAddress1'.