Easy SQL syntax question

Dim varBookID as String
varBookID = request.params("BookID")

Dim varBookNo as String
varBookNo = request.params("BookNo")

***What is incorrect with the string concatenation below? I know the SQL syntax is incorrect, but i cannot locate the problem.***

DBCommand = New OleDbDataAdapter("SELECT * FROM Books WHERE BookID=" & "'" & varBookID & "'" AND BookNo= & "'" & varBookNo & "'"", DBConn)

You have a string with a part that ends after

"SELECT * FROM Books WHERE BookID=" & "'" & varBookID & "'"

Then comes

AND BookNo= & "'" & varBookNo & "'""

That should give you a compiler syntax error right there. Simplified:

"SELECT * FROM Books WHERE BookID='" & varBookID & "' AND BookNo='" & varBookNo & "'"

Among other things, it is because of stuff like this that you should use
Stored procedures and SqlParameters, which eliminate these kinds of tedious string concatenations.
You can find many examples on the net

|||It looks like you've got an extra quote mark at the end.
sql

Easy question: Copy Image to a File

How I can copy an image to a file?

I want to use SP

I use MSDE, textcopy is not installed

I can use bcp, but I don't understand the syntax for image filed

Something like this

bcp "SELECT [ImageFiled] FROM MyDatabase..MYTable WHERE [ID]=1111" queryout c:\test.BMP -c -Sservername -Usa -Ppassword

This statement dont work.
Please I need an example.

Thanks,
Sorry for my Englishhttp://www.databasejournal.com/features/mssql/article.php/1443521 to feature the task.

Easy Query Syntax Question

Good morning, experts.
Got a syntactical snag I need a fresh pair of eyes on. I've looked at this
query too long and I know that the fix is probably staring me in the face bu
t
I can't see the "forest for the trees", as it were.
SELECT * FROM
((SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30) OrgNtc
INNER JOIN
(SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15) DsmNtc
ON OrgNtc.CDS_04 = DsmNtc.CDS_04
AND OrgNtc.CDS_07 = DsmNtc.CDS_07
AND OrgNtc.CDS_09 = DsmNtc.CDS_09) CS
INNER JOIN
(SELECT CaseHdr_ID, NM1_ID
FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')) NM
ON CS.CaseHdr_ID = NM.CaseHdr_ID
INNER JOIN
(SELECT REF_02, NM1_ID
FROM EBN_REF WHERE REF_01 = 'SY') RF
ON NM.NM1_ID = RF.NM1_ID
I get a "Invalid syntax near CS, NM and RF" error when I try to execute the
whole thing. I'm betting I just need to throw some open/close parens in
there somewhere but not sure where. If anybody could help me tweak this
thing so I can get it to parse I'd be most grateful.Try,
SELECT * FROM
(
select *
from
(
SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30\
) OrgNtc
INNER JOIN
(
SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15
) DsmNtc
ON OrgNtc.CDS_04 = DsmNtc.CDS_04
AND OrgNtc.CDS_07 = DsmNtc.CDS_07
AND OrgNtc.CDS_09 = DsmNtc.CDS_09
) as CS
INNER JOIN
(
SELECT CaseHdr_ID, NM1_ID
FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')
) as NM
ON CS.CaseHdr_ID = NM.CaseHdr_ID
INNER JOIN
(
SELECT REF_02, NM1_ID
FROM EBN_REF WHERE REF_01 = 'SY'
) as RF
ON NM.NM1_ID = RF.NM1_ID
AMB
"Bob St. Aubyn" wrote:

> Good morning, experts.
> Got a syntactical snag I need a fresh pair of eyes on. I've looked at thi
s
> query too long and I know that the fix is probably staring me in the face
but
> I can't see the "forest for the trees", as it were.
> SELECT * FROM
> ((SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30) OrgNtc
> INNER JOIN
> (SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15) DsmNtc
> ON OrgNtc.CDS_04 = DsmNtc.CDS_04
> AND OrgNtc.CDS_07 = DsmNtc.CDS_07
> AND OrgNtc.CDS_09 = DsmNtc.CDS_09) CS
> INNER JOIN
> (SELECT CaseHdr_ID, NM1_ID
> FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')) NM
> ON CS.CaseHdr_ID = NM.CaseHdr_ID
> INNER JOIN
> (SELECT REF_02, NM1_ID
> FROM EBN_REF WHERE REF_01 = 'SY') RF
> ON NM.NM1_ID = RF.NM1_ID
> I get a "Invalid syntax near CS, NM and RF" error when I try to execute th
e
> whole thing. I'm betting I just need to throw some open/close parens in
> there somewhere but not sure where. If anybody could help me tweak this
> thing so I can get it to parse I'd be most grateful.|||Thanks Alejandro. Worked and made sense.
I had to make one slight modification to get it to parse: Changed the new
"select * from" (in lower case) to "SELECT OrgNtc.CDS_04, OrgNtc.CaseHdr_ID
FROM ..." because both of those columns are selected in each of the
subqueries combined to make CS. Just had to explicitly specify which one I
wanted to return.
"Alejandro Mesa" wrote:
> Try,
> SELECT * FROM
> (
> select *
> from
> (
> SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30\
> ) OrgNtc
> INNER JOIN
> (
> SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15
> ) DsmNtc
> ON OrgNtc.CDS_04 = DsmNtc.CDS_04
> AND OrgNtc.CDS_07 = DsmNtc.CDS_07
> AND OrgNtc.CDS_09 = DsmNtc.CDS_09
> ) as CS
> INNER JOIN
> (
> SELECT CaseHdr_ID, NM1_ID
> FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')
> ) as NM
> ON CS.CaseHdr_ID = NM.CaseHdr_ID
> INNER JOIN
> (
> SELECT REF_02, NM1_ID
> FROM EBN_REF WHERE REF_01 = 'SY'
> ) as RF
> ON NM.NM1_ID = RF.NM1_ID
>
> AMB
> "Bob St. Aubyn" wrote:
>

Dynamic Where

Not to familiar with storedprocedures or their ablities. I'm attempting to
shorten syntax of procedure. Previously I had evaluated all the variables
and then set the statement. Is it possible to have static portions and add
to other portions as I have attempted below in where clause?
CREATE PROCEDURE dbo.TaskView
(@.udi int,
@.cName nvarchar(50),
@.cat varchar(100),
@.sdate smalldatetime,
@.fdate smalldatetime,
@.pastdue bit,
@.inactive bit,
@.utype int
)
AS
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid = dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
@.cat) AND (dbo.facility.cNam LIKE @.cName)
if @.pastdue = 1
begin
AND (dbo.task.DueDate < GETDATE())
end
if @.sdate=null or @.fdate=null
begin
AND (dbo.task.DueDate >Getdate()-1)
end
else
begin
AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
end
if @.utype>0
begin
AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
end
else
begin
AND (dbo.utable.udi = @.udi)
end
GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
ORDER BY dbo.task.DueDateI don't know what you mean by:
"Previously I had evaluated all the variables
and then set the statement."
If I understand you correctly then you will need to use dynamic sql but
trust me you don't want to do that.
You could try something like this:
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid =
dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid
INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi =
utable_1.udi
WHERE (dbo.task.inactive = CASE WHEN @.inactive IS NULL THEN
dbo.task.inactive ELSE @.inactive END) etc, etc, etc...
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Nathan Carroll" <nath_an@.thelosth_orizon.com> wrote in message
news:OgljnRX6FHA.1036@.tk2msftngp13.phx.gbl...
> Not to familiar with storedprocedures or their ablities. I'm attempting
> to
> shorten syntax of procedure. Previously I had evaluated all the variables
> and then set the statement. Is it possible to have static portions and
> add
> to other portions as I have attempted below in where clause?
>
> CREATE PROCEDURE dbo.TaskView
> (@.udi int,
> @.cName nvarchar(50),
> @.cat varchar(100),
> @.sdate smalldatetime,
> @.fdate smalldatetime,
> @.pastdue bit,
> @.inactive bit,
> @.utype int
> )
> AS
> SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
> FROM dbo.facility INNER JOIN
> dbo.category ON dbo.facility.fid = dbo.category.fid
> INNER JOIN
> dbo.task ON dbo.facility.fid = dbo.task.fid AND
> dbo.category.caID = dbo.task.caID INNER JOIN
> dbo.response ON dbo.task.tid = dbo.response.tid
> INNER JOIN
> dbo.utable ON dbo.response.udi = dbo.utable.udi
> INNER JOIN
> dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
> JOIN
> dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
> WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
> @.cat) AND (dbo.facility.cNam LIKE @.cName)
> if @.pastdue = 1
> begin
> AND (dbo.task.DueDate < GETDATE())
> end
> if @.sdate=null or @.fdate=null
> begin
> AND (dbo.task.DueDate >Getdate()-1)
> end
> else
> begin
> AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
> end
> if @.utype>0
> begin
> AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
> end
> else
> begin
> AND (dbo.utable.udi = @.udi)
> end
> GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
> ORDER BY dbo.task.DueDate
>|||yes you can did not get your question. You can use th + operator to
concate for dynamic sql
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Dynamic Where

Not to familiar with storedprocedures or their ablities. I'm attempting to
shorten syntax of procedure. Previously I had evaluated all the variables
and then set the statement. Is it possible to have static portions and add
to other portions as I have attempted below in where clause?
CREATE PROCEDURE dbo.TaskView
(@.udi int,
@.cName nvarchar(50),
@.cat varchar(100),
@.sdate smalldatetime,
@.fdate smalldatetime,
@.pastdue bit,
@.inactive bit,
@.utype int
)
AS
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid = dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
@.cat) AND (dbo.facility.cNam LIKE @.cName)
if @.pastdue = 1
begin
AND (dbo.task.DueDate < GETDATE())
end
if @.sdate=null or @.fdate=null
begin
AND (dbo.task.DueDate >Getdate()-1)
end
else
begin
AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
end
if @.utype>0
begin
AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
end
else
begin
AND (dbo.utable.udi = @.udi)
end
GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
ORDER BY dbo.task.DueDate
I don't know what you mean by:
"Previously I had evaluated all the variables
and then set the statement."
If I understand you correctly then you will need to use dynamic sql but
trust me you don't want to do that.
You could try something like this:
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid =
dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid
INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi =
utable_1.udi
WHERE (dbo.task.inactive = CASE WHEN @.inactive IS NULL THEN
dbo.task.inactive ELSE @.inactive END) etc, etc, etc...
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Nathan Carroll" <nath_an@.thelosth_orizon.com> wrote in message
news:OgljnRX6FHA.1036@.tk2msftngp13.phx.gbl...
> Not to familiar with storedprocedures or their ablities. I'm attempting
> to
> shorten syntax of procedure. Previously I had evaluated all the variables
> and then set the statement. Is it possible to have static portions and
> add
> to other portions as I have attempted below in where clause?
>
> CREATE PROCEDURE dbo.TaskView
> (@.udi int,
> @.cName nvarchar(50),
> @.cat varchar(100),
> @.sdate smalldatetime,
> @.fdate smalldatetime,
> @.pastdue bit,
> @.inactive bit,
> @.utype int
> )
> AS
> SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
> FROM dbo.facility INNER JOIN
> dbo.category ON dbo.facility.fid = dbo.category.fid
> INNER JOIN
> dbo.task ON dbo.facility.fid = dbo.task.fid AND
> dbo.category.caID = dbo.task.caID INNER JOIN
> dbo.response ON dbo.task.tid = dbo.response.tid
> INNER JOIN
> dbo.utable ON dbo.response.udi = dbo.utable.udi
> INNER JOIN
> dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
> JOIN
> dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
> WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
> @.cat) AND (dbo.facility.cNam LIKE @.cName)
> if @.pastdue = 1
> begin
> AND (dbo.task.DueDate < GETDATE())
> end
> if @.sdate=null or @.fdate=null
> begin
> AND (dbo.task.DueDate >Getdate()-1)
> end
> else
> begin
> AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
> end
> if @.utype>0
> begin
> AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
> end
> else
> begin
> AND (dbo.utable.udi = @.udi)
> end
> GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
> ORDER BY dbo.task.DueDate
>
|||yes you can did not get your question. You can use th + operator to
concate for dynamic sql
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Dynamic Where

Not to familiar with storedprocedures or their ablities. I'm attempting to
shorten syntax of procedure. Previously I had evaluated all the variables
and then set the statement. Is it possible to have static portions and add
to other portions as I have attempted below in where clause?
CREATE PROCEDURE dbo.TaskView
(@.udi int,
@.cName nvarchar(50),
@.cat varchar(100),
@.sdate smalldatetime,
@.fdate smalldatetime,
@.pastdue bit,
@.inactive bit,
@.utype int
)
AS
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid = dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
@.cat) AND (dbo.facility.cNam LIKE @.cName)
if @.pastdue = 1
begin
AND (dbo.task.DueDate < GETDATE())
end
if @.sdate=null or @.fdate=null
begin
AND (dbo.task.DueDate >Getdate()-1)
end
else
begin
AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
end
if @.utype>0
begin
AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
end
else
begin
AND (dbo.utable.udi = @.udi)
end
GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
ORDER BY dbo.task.DueDateI don't know what you mean by:
"Previously I had evaluated all the variables
and then set the statement."
If I understand you correctly then you will need to use dynamic sql but
trust me you don't want to do that.
You could try something like this:
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid =dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid
INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi =utable_1.udi
WHERE (dbo.task.inactive = CASE WHEN @.inactive IS NULL THEN
dbo.task.inactive ELSE @.inactive END) etc, etc, etc...
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Nathan Carroll" <nath_an@.thelosth_orizon.com> wrote in message
news:OgljnRX6FHA.1036@.tk2msftngp13.phx.gbl...
> Not to familiar with storedprocedures or their ablities. I'm attempting
> to
> shorten syntax of procedure. Previously I had evaluated all the variables
> and then set the statement. Is it possible to have static portions and
> add
> to other portions as I have attempted below in where clause?
>
> CREATE PROCEDURE dbo.TaskView
> (@.udi int,
> @.cName nvarchar(50),
> @.cat varchar(100),
> @.sdate smalldatetime,
> @.fdate smalldatetime,
> @.pastdue bit,
> @.inactive bit,
> @.utype int
> )
> AS
> SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
> FROM dbo.facility INNER JOIN
> dbo.category ON dbo.facility.fid = dbo.category.fid
> INNER JOIN
> dbo.task ON dbo.facility.fid = dbo.task.fid AND
> dbo.category.caID = dbo.task.caID INNER JOIN
> dbo.response ON dbo.task.tid = dbo.response.tid
> INNER JOIN
> dbo.utable ON dbo.response.udi = dbo.utable.udi
> INNER JOIN
> dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
> JOIN
> dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
> WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
> @.cat) AND (dbo.facility.cNam LIKE @.cName)
> if @.pastdue = 1
> begin
> AND (dbo.task.DueDate < GETDATE())
> end
> if @.sdate=null or @.fdate=null
> begin
> AND (dbo.task.DueDate >Getdate()-1)
> end
> else
> begin
> AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
> end
> if @.utype>0
> begin
> AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
> end
> else
> begin
> AND (dbo.utable.udi = @.udi)
> end
> GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
> ORDER BY dbo.task.DueDate
>|||yes you can did not get your question. You can use th + operator to
concate for dynamic sql
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Dynamic SQL Syntax H_ll Please Help

This forum has helped me get to this point a couple times with this project BUT I just can't seem to get the syntax correct. Either one of these two statements do exactly what I want them to do:
SELECT @.RtnValue = Column0 FROM MyTable WHERE RowIndex = @.RIndex
SELECT @.RtnValue = (SELECT Column0 FROM MyTable WHERE RowIndex = @.RIndex)

@.RtnValue is the value the program will work with.

The only problem is that Column0 and @.RIndex need to be dynamic so I can index through each Column and Row of the table.

This is the code I am trying to use to do this dynamically, naturally it will be in two while loop to index through each Column and Row

DECLARE @.RtnValue smallint
SET @.RtnValue = 0

DECLARE @.RIndex smallint
SET @.RIndex = 20

DECLARE @.ColumnName varchar(10)
SET @.ColumnName = 'Column0'

DECLARE @.MySelectString varchar(200)
SET @.MySelectString0 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = ' + @.RIndex + ' )'
--SET @.MySelectString1 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = 1 )'

EXEC( @.MySelectString0 )
--EXEC( @.MySelectString1 )

@.MySelectString0 produces this error:
Server: Msg 245, Level 16, State 1, Line 23
Syntax error converting the varchar value 'SELECT @.RtnValue = ( SELECT Column0 FROM MyTable WHERE RowIndex = ' to a column of data type smallint.

@.MySelectString1 produces this error:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable'@.RtnValue'.

I have tried many different combinations of syntax but can not seem to get the magic combination. Can someone tell me the correct syntax to get this to work.

Thank you in advance.

try something like this :

SET @.MySelectString0 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = ' + CONVERT(VARCHAR,@.RIndex) + ' )'

|||

Thank You for your responce I will try your solution. I sure hope it works I have been stuck on this problem to long Thanks again

Dynamic SQL question

Can someone tell me why the exec statement throws the following error?
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

Code Snippet

create table #t (Val1 smallint, Val2 smallint )
go
DECLARE @.Diff as int;
set nocount on

insert into #t values(10, 15)

--Why does this work?
SET @.Diff=(SELECT Val2 FROM #t) - (SELECT Val1 FROM #t)
select @.Diff

--And yet this does not?
EXEC('(SELECT Val2 FROM #t) - (SELECT Val1 FROM #t)')

drop table #t
go

Thanks

Colin

It is because you dont have a SELECT or anything in your dynamic sql.

change to:

Code Snippet

EXEC('SELECT (SELECT Val2 FROM #t) - (SELECT Val1 FROM #t)')

and it works fine.

Dynamic SQL Question

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.):
DECLARE @.sCalldate varchar(10)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.sEmployeeNameId = '0'
SELECT @.sAgentid ='0'
SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = ' + @.sCallDate +
' AND ISNULL(sRawLogout, ''x'') = ''x'''
EXEC (@.SQL)
How can I make this work?
Thanks,
Ninelhi ninel,
Please, post DDL instructions but at first, try to sustitute this line for
the another one:

> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)

> set @.sql = ('
SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''')

> EXEC (@.SQL)
"ninel" wrote:

> 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.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>|||I see no need for dynamic SQL here:
DECLARE @.sCalldate varchar(10)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.RC=Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = @.sCallDate
AND ISNULL(sRawLogout, 'x') = 'x'
Razvan|||Hi!
declare
@.sql varchar(8000),
@.sCalidate varchar(10),
@.RC int;
set @.sql = 'select @.RC = count(*) from from tmPunchTime where scalldate =
@.sCallDate and is null(sRawLogout, ''x'') = ''x''';
exec sp_executesql @.sql, N'@.RC int out, @.sCalidate varchar(10)', @.RS out,
@.sCalidate = @.sCalidate;
select @.RC
Micle.
"ninel" <ngorbunov@.onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:orOdnbUkTPNtF33fRVn_vA@.giganews.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.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>|||Hi Ninel,
I agree with Razvan Socol that there is no need for Dynamic SQL.
Please visit this link and view When not to use Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html ( An authority on Dynamic
SQL)
If in your case you donot know TableName or Parameters are changing
then Micle approach is what you need
Please let me know if it clear your doubts.
With warm regards
Jatinder|||No need to use dynmaic sql to get the results you want. Please see example
below.
DECLARE @.sCalldate varchar(10)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
DECLARE @.sEmployeeNameId int
DECLARE @.sAgentid int
SELECT @.sCalldate ='20050712'
SELECT @.sEmployeeNameId = '0'
SELECT @.sAgentid ='0'
CREATE TABLE #tmPunchTime
(
Scalldate datetime,
sRawLogout sysname
)
Insert #tmPunchTime
Values ('20050712','x')
Insert #tmPunchTime
Values ('20050712','x')
Insert #tmPunchTime
Values ('20050712','x')
SELECT @.RC =(SELECT Count(*)
FROM #tmPunchTime
WHERE convert(nvarchar,scalldate,112) = @.sCallDate
AND ISNULL(sRawLogout, 'x') = 'x')
SELECT @.RC
DROP TABLE #tmPunchTime
"ninel" wrote:

> 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.):
> DECLARE @.sCalldate varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sEmployeeNameId = '0'
> SELECT @.sAgentid ='0'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@.SQL)
> How can I make this work?
> Thanks,
> Ninel
>

dynamic sql query syntax

Hi friends
I need ur help with syntax of following dynamic sql.actually it is simple version of what am doing but i know for sure this line actually causing the error.

DECLARE @.p_taskentrydtfilter nvarchar(50),@.taskentrydttag nvarchar(100)
declare @.p_taskentrydt DATETIME

set @.p_taskentrydtfilter= '>='
set @.p_taskentrydt = '20050609'

select @.taskentrydttag=case when (@.p_taskentrydt='' OR @.p_taskentrydt is null) then '' else
' and task.entrydt '+
@.p_taskentrydtfilter+''+@.p_taskentrydt+''' and task.entrydt <'''+dateadd(d,1,@.p_taskentrydt)+'' end

as u can see am storing a CASE stmt in a variable but it gives me error saying
"Syntax error converting datetime from character string."
any ideas .Thanks

Cheers

Hi,

I think its your date format. try setting your @.p_taskentrydt to other date date formats such as mm/dd/yyyy

cheers,

Paul June A. Domag

|||@.p_taskentrydtfilter+''+@.p_taskentrydt + '''

should be:

@.p_taskentrydtfilter+''''+@.p_taskentrydt + '''

But you should be using sp_executesql instead of dynamically concatenating variables that can be set by caller for example. See BOL for more details on how to use sp_executesql to execute parameterized SQL statements.

|||Thanks Umachandar
That worked nicely

dynamic SQL error

Help,
I have the following procedure declared as a test for dynamic sql. The
resulting SQL statment is correct but I am getting syntax errors when the
dynamic SQL runs. taking the body of the procedure and running it from SQL
Analyzer window
creates the correct result. If I rewrite it to remove the parameters it
alos runs correctly. What am I missing to have this run corrcetly with
parameters
Declare @.Accountname varchar(50)
DECLARE @.@.OrgID int
DECLARE @.@.MonthID int
DECLARE @.@.W int
DECLARE @.@.AccountID int
DECLARE @.SQL nvarchar(4000), @.ParmDefinition nvarchar(500)
Set @.@.Orgid=6
Set @.@.Monthid=1
Set @.@.W=2
Set @.@.Accountid=3
Set @.Accountname = 'IP'
Begin
Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
[DatW], [DatAccountID], [DatValue] )
SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, 99
FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W int, @.@.AccountID
int'
exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W= 2,
@.@.AccountID= 3
End
create PROCEDURE [dbo].[test]
AS
Declare @.SQL nvarchar(4000), @.ParmDefinition nvarchar
Begin
Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
[DatW], [DatAccountID], [DatValue] )
SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, (SELECT
[DBo].[CalcIP](@.@.OrgId,@.@.MonthId,@.@.W))
FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W int, @.@.AccountID
int'
exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W= 2,
@.@.AccountID= 3
print @.SQL
End
EXEC [HYP-MOR].[dynasight].[TEST]
yeild the following result
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.@.OrgId'.
INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID], [DatW],
[DatAccountID], [DatValue] )
SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, 99
FROM dynasight.BudgetAccounts where AccountID = @.@.AccountIDHi,
are your sql server in collation CS ? Wich I think so ?
So there is a mismatch between the declare @.@.OrgID and the use in your
stored proc : @.@.OrgId
A +
ahuntertate a écrit :
> Help,
> I have the following procedure declared as a test for dynamic sql. The
> resulting SQL statment is correct but I am getting syntax errors when the
> dynamic SQL runs. taking the body of the procedure and running it from SQ
L
> Analyzer window
> creates the correct result. If I rewrite it to remove the parameters it
> alos runs correctly. What am I missing to have this run corrcetly with
> parameters
> Declare @.Accountname varchar(50)
> DECLARE @.@.OrgID int
> DECLARE @.@.MonthID int
> DECLARE @.@.W int
> DECLARE @.@.AccountID int
> DECLARE @.SQL nvarchar(4000), @.ParmDefinition nvarchar(500)
> Set @.@.Orgid=6
> Set @.@.Monthid=1
> Set @.@.W=2
> Set @.@.Accountid=3
> Set @.Accountname = 'IP'
>
> Begin
> Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
> [DatW], [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, 99
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
> SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W int, @.@.AccountI
D
> int'
>
> exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W=
2,
> @.@.AccountID= 3
> End
>
>
> create PROCEDURE [dbo].[test]
> AS
> Declare @.SQL nvarchar(4000), @.ParmDefinition nvarchar
>
> Begin
> Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
> [DatW], [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, (SELECT
> [DBo].[CalcIP](@.@.OrgId,@.@.MonthId,@.@.W))
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
> SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W int, @.@.AccountI
D
> int'
>
> exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W=
2,
> @.@.AccountID= 3
> print @.SQL
> End
>
> EXEC [HYP-MOR].[dynasight].[TEST]
> yeild the following result
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 137, Level 15, State 1, Line 2
> Must declare the variable '@.@.OrgId'.
> INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID], [DatW],
> [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, 99
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||I get a different error message (stating that the table doesn't exist, which
is correct). Make sure
that you use correct casing. Perhaps you are on a case sensitive SQL Server.
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ahuntertate" <ahuntertate@.discussions.microsoft.com> wrote in message
news:8B7E4A21-FA48-4BA5-8DC6-104666C3C1E5@.microsoft.com...
> Help,
> I have the following procedure declared as a test for dynamic sql. The
> resulting SQL statment is correct but I am getting syntax errors when the
> dynamic SQL runs. taking the body of the procedure and running it from SQ
L
> Analyzer window
> creates the correct result. If I rewrite it to remove the parameters it
> alos runs correctly. What am I missing to have this run corrcetly with
> parameters
> Declare @.Accountname varchar(50)
> DECLARE @.@.OrgID int
> DECLARE @.@.MonthID int
> DECLARE @.@.W int
> DECLARE @.@.AccountID int
> DECLARE @.SQL nvarchar(4000), @.ParmDefinition nvarchar(500)
> Set @.@.Orgid=6
> Set @.@.Monthid=1
> Set @.@.W=2
> Set @.@.Accountid=3
> Set @.Accountname = 'IP'
>
> Begin
> Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
> [DatW], [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, 99
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
> SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W int, @.@.AccountI
D
> int'
>
> exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W=
2,
> @.@.AccountID= 3
> End
>
>
> create PROCEDURE [dbo].[test]
> AS
> Declare @.SQL nvarchar(4000), @.ParmDefinition nvarchar
>
> Begin
> Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
> [DatW], [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, (SELECT
> [DBo].[CalcIP](@.@.OrgId,@.@.MonthId,@.@.W))
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
> SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W int, @.@.AccountI
D
> int'
>
> exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W=
2,
> @.@.AccountID= 3
> print @.SQL
> End
>
> EXEC [HYP-MOR].[dynasight].[TEST]
> yeild the following result
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 137, Level 15, State 1, Line 2
> Must declare the variable '@.@.OrgId'.
> INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID], [DatW],
> [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, 99
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID
>|||Change this sentence into SP and probe:
Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
[DatW], [DatAccountID], [DatValue] )
SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, (SELECT
[DBo].[CalcIP]( '
+ convert(nvarchar, @.@.OrgId) +','
+ convert(nvarchar, @.@.MonthId) + ','
+ convert(nvarchar, @.@.W)
+ '))
FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
I had the same problem in a function call inside dynamic sql query. I
replaced variables by value variables.
Cordially,
Richard_SQL
"ahuntertate" wrote:

> Help,
> I have the following procedure declared as a test for dynamic sql. The
> resulting SQL statment is correct but I am getting syntax errors when the
> dynamic SQL runs. taking the body of the procedure and running it from SQ
L
> Analyzer window
> creates the correct result. If I rewrite it to remove the parameters it
> alos runs correctly. What am I missing to have this run corrcetly with
> parameters
> Declare @.Accountname varchar(50)
> DECLARE @.@.OrgID int
> DECLARE @.@.MonthID int
> DECLARE @.@.W int
> DECLARE @.@.AccountID int
> DECLARE @.SQL nvarchar(4000), @.ParmDefinition nvarchar(500)
> Set @.@.Orgid=6
> Set @.@.Monthid=1
> Set @.@.W=2
> Set @.@.Accountid=3
> Set @.Accountname = 'IP'
>
> Begin
> Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
> [DatW], [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, 99
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
> SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W int, @.@.AccountI
D
> int'
>
> exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W=
2,
> @.@.AccountID= 3
> End
>
>
> create PROCEDURE [dbo].[test]
> AS
> Declare @.SQL nvarchar(4000), @.ParmDefinition nvarchar
>
> Begin
> Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID],
> [DatW], [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, (SELECT
> [DBo].[CalcIP](@.@.OrgId,@.@.MonthId,@.@.W))
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
> SET @.ParmDefinition = N'@.@.OrgID int, @.@.MonthID int, @.@.W int, @.@.AccountI
D
> int'
>
> exec sp_executesql @.SQL,@.ParmDefinition,@.@.Orgid= 6,@.@.MonthID = 1, @.@.W=
2,
> @.@.AccountID= 3
> print @.SQL
> End
>
> EXEC [HYP-MOR].[dynasight].[TEST]
> yeild the following result
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 137, Level 15, State 1, Line 2
> Must declare the variable '@.@.OrgId'.
> INSERT INTO [dynasight].[BudgetData]( [DatOrgID], [DatMonthID], [DatW],
> [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, 99
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID
>|||Richard (Richard@.discussions.microsoft.com) writes:
> Change this sentence into SP and probe:
> Set @.SQL = N'INSERT INTO [dynasight].[BudgetData]( [DatOrgID],
> [DatMonthID],
> [DatW], [DatAccountID], [DatValue] )
> SELECT @.@.OrgId,@.@.MonthId,@.@.W,@.@.AccountId, (SELECT
> [DBo].[CalcIP]( '
> + convert(nvarchar, @.@.OrgId) +','
> + convert(nvarchar, @.@.MonthId) + ','
> + convert(nvarchar, @.@.W)
> + '))
> FROM dynasight.BudgetAccounts where AccountID = @.@.AccountID'
> I had the same problem in a function call inside dynamic sql query. I
> replaced variables by value variables.
No, that's the wrong way of doing it. ahuntertate used sp_executesql
and passed parameters to it, which is the right way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ahuntertate (ahuntertate@.discussions.microsoft.com) writes:
> create PROCEDURE [dbo].[test]
> AS
> Declare @.SQL nvarchar(4000), @.ParmDefinition nvarchar
nvarchar for the @.ParmDefinition is not good. That is the same as
nvarchar(1).

> EXEC [HYP-MOR].[dynasight].[TEST]
> yeild the following result
The schema/owner in the EXEC statement does not match CREATE PROCEDURE
statement.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx