Expanding sp_executesql Parameters?

Hi,
Is it possible, when using sp_executesql, to see the SQL statement with the
parameters expanded?
I'm losing years of life trying to get the following dynamic SQL statement t
o work:
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
SET @.sSQL = N'SELECT OrderNo, PropStreet1
FROM tbl_Orders
AND CONTAINS(PropStreet1, '' " + @.PropStreet1 " '')'
SET @.Params = N' @.AddressStreet VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I don't get a syntax error; but I don't get a result set either. Whereas I d
o get a result set if I just run the plain-Jane Select query (w/o using sp_e
xecutesql).
Is there a way, in Profiler for example, that I could see how SQL Server exp
ands the parameter? That I could see the final SQL statement?
Thank you.
--
--
Mark HolahanI think I see what you're trying to do here, but just to be sure, can you po
st the exact "plain-Jane SQL query" you are running?
Thx,
Mike C
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:u7wQg5nHF
HA.3500@.TK2MSFTNGP14.phx.gbl...
Hi,
Is it possible, when using sp_executesql, to see the SQL statement with the
parameters expanded?
I'm losing years of life trying to get the following dynamic SQL statement t
o work:
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
SET @.sSQL = N'SELECT OrderNo, PropStreet1
FROM tbl_Orders
AND CONTAINS(PropStreet1, '' " + @.PropStreet1 " '')'
SET @.Params = N' @.AddressStreet VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I don't get a syntax error; but I don't get a result set either. Whereas I d
o get a result set if I just run the plain-Jane Select query (w/o using sp_e
xecutesql).
Is there a way, in Profiler for example, that I could see how SQL Server exp
ands the parameter? That I could see the final SQL statement?
Thank you.
--
--
Mark Holahan|||Mike,
Plain Jane:
SELECT OrderNo, PropStreet1
FROM tbl_Orders
WHERE CONTAINS(PropStreet1, ' "0-14115 Ironwood Dr" ')
I see that I left out the word "WHERE" below; this was an oversight.
Thanks.
"Michael C#" <xyz@.yomomma.com> wrote in message news:uOIjvKpHFHA.3484@.TK2MSF
TNGP12.phx.gbl...
I think I see what you're trying to do here, but just to be sure, can you po
st the exact "plain-Jane SQL query" you are running?
Thx,
Mike C
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:u7wQg5nHF
HA.3500@.TK2MSFTNGP14.phx.gbl...
Hi,
Is it possible, when using sp_executesql, to see the SQL statement with the
parameters expanded?
I'm losing years of life trying to get the following dynamic SQL statement t
o work:
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
SET @.sSQL = N'SELECT OrderNo, PropStreet1
FROM tbl_Orders
AND CONTAINS(PropStreet1, '' " + @.PropStreet1 " '')'
SET @.Params = N' @.AddressStreet VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I don't get a syntax error; but I don't get a result set either. Whereas I d
o get a result set if I just run the plain-Jane Select query (w/o using sp_e
xecutesql).
Is there a way, in Profiler for example, that I could see how SQL Server exp
ands the parameter? That I could see the final SQL statement?
Thank you.
--
--
Mark Holahan|||I'm having a little trouble installing Full-Text Search on my beat-up little
lap-top over here. So with the caveat that I haven't actually tested it, y
ou might try the following:
DECLARE @.Params NVARCHAR(50)
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
-- changed the parameter in your query from @.PropStreet1 to @.AddressStreet1
and got rid of the + sign
SET @.sSQL = N'SELECT OrderNo, PropStreet1 FROM tbl_Orders WHERE CONTAINS(Pro
pStreet1, '' " @.AddressStreet1 " '')'
-- changed @.AddressStreet to @.AddressStreet1
SET @.Params = N' @.AddressStreet1 VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I'll see if I can clean this little box up enough to get Full-Text Search in
stalled (it really needs to be re-formatted... ah well...)
Thx
Mike C.
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:uruEiRpHF
HA.720@.TK2MSFTNGP10.phx.gbl...
Mike,
Plain Jane:
SELECT OrderNo, PropStreet1
FROM tbl_Orders
WHERE CONTAINS(PropStreet1, ' "0-14115 Ironwood Dr" ')
I see that I left out the word "WHERE" below; this was an oversight.
Thanks.
"Michael C#" <xyz@.yomomma.com> wrote in message news:uOIjvKpHFHA.3484@.TK2MSF
TNGP12.phx.gbl...
I think I see what you're trying to do here, but just to be sure, can you po
st the exact "plain-Jane SQL query" you are running?
Thx,
Mike C
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:u7wQg5nHF
HA.3500@.TK2MSFTNGP14.phx.gbl...
Hi,
Is it possible, when using sp_executesql, to see the SQL statement with the
parameters expanded?
I'm losing years of life trying to get the following dynamic SQL statement t
o work:
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
SET @.sSQL = N'SELECT OrderNo, PropStreet1
FROM tbl_Orders
AND CONTAINS(PropStreet1, '' " + @.PropStreet1 " '')'
SET @.Params = N' @.AddressStreet VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I don't get a syntax error; but I don't get a result set either. Whereas I d
o get a result set if I just run the plain-Jane Select query (w/o using sp_e
xecutesql).
Is there a way, in Profiler for example, that I could see how SQL Server exp
ands the parameter? That I could see the final SQL statement?
Thank you.
--
--
Mark Holahan|||Mike,
Thanks for your time. I'm going to post the question a little differently.
Mark
"Michael C#" <xyz@.yomomma.com> wrote in message news:ufFBc8pHFHA.4004@.TK2MSF
TNGP10.phx.gbl...
I'm having a little trouble installing Full-Text Search on my beat-up little
lap-top over here. So with the caveat that I haven't actually tested it, y
ou might try the following:
DECLARE @.Params NVARCHAR(50)
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
-- changed the parameter in your query from @.PropStreet1 to @.AddressStreet1
and got rid of the + sign
SET @.sSQL = N'SELECT OrderNo, PropStreet1 FROM tbl_Orders WHERE CONTAINS(Pro
pStreet1, '' " @.AddressStreet1 " '')'
-- changed @.AddressStreet to @.AddressStreet1
SET @.Params = N' @.AddressStreet1 VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I'll see if I can clean this little box up enough to get Full-Text Search in
stalled (it really needs to be re-formatted... ah well...)
Thx
Mike C.
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:uruEiRpHF
HA.720@.TK2MSFTNGP10.phx.gbl...
Mike,
Plain Jane:
SELECT OrderNo, PropStreet1
FROM tbl_Orders
WHERE CONTAINS(PropStreet1, ' "0-14115 Ironwood Dr" ')
I see that I left out the word "WHERE" below; this was an oversight.
Thanks.
"Michael C#" <xyz@.yomomma.com> wrote in message news:uOIjvKpHFHA.3484@.TK2MSF
TNGP12.phx.gbl...
I think I see what you're trying to do here, but just to be sure, can you po
st the exact "plain-Jane SQL query" you are running?
Thx,
Mike C
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:u7wQg5nHF
HA.3500@.TK2MSFTNGP14.phx.gbl...
Hi,
Is it possible, when using sp_executesql, to see the SQL statement with the
parameters expanded?
I'm losing years of life trying to get the following dynamic SQL statement t
o work:
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
SET @.sSQL = N'SELECT OrderNo, PropStreet1
FROM tbl_Orders
AND CONTAINS(PropStreet1, '' " + @.PropStreet1 " '')'
SET @.Params = N' @.AddressStreet VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I don't get a syntax error; but I don't get a result set either. Whereas I d
o get a result set if I just run the plain-Jane Select query (w/o using sp_e
xecutesql).
Is there a way, in Profiler for example, that I could see how SQL Server exp
ands the parameter? That I could see the final SQL statement?
Thank you.
--
--
Mark Holahan|||No prob. But if you don't mind my asking, what happened when you tried the
change? Did it generate an error or what? To answer your other question, I
don't think you can view the SQL with your parameter replaced by your value
, because SQL Server doesn't do a simple string-type replacement when you in
voke sp_executesql. You can add a PRINT @.sSQL right behind the SET @.sSQL st
atement, but it won't have your replacement value in it.
Thx,
Mike C.
"Mark Holahan" <mark.holahan@.unifiedllc.com> wrote in message news:%234xWobq
HFHA.2936@.TK2MSFTNGP15.phx.gbl...
Mike,
Thanks for your time. I'm going to post the question a little differently.
Mark
"Michael C#" <xyz@.yomomma.com> wrote in message news:ufFBc8pHFHA.4004@.TK2MSF
TNGP10.phx.gbl...
I'm having a little trouble installing Full-Text Search on my beat-up little
lap-top over here. So with the caveat that I haven't actually tested it, y
ou might try the following:
DECLARE @.Params NVARCHAR(50)
DECLARE @.sSQL NVARCHAR(1000)
DECLARE @.PropStreet1 VARCHAR(50)
SET @.PropStreet1 = '0-14115 Ironwood Dr'
-- changed the parameter in your query from @.PropStreet1 to @.AddressStreet1
and got rid of the + sign
SET @.sSQL = N'SELECT OrderNo, PropStreet1 FROM tbl_Orders WHERE CONTAINS(Pro
pStreet1, '' " @.AddressStreet1 " '')'
-- changed @.AddressStreet to @.AddressStreet1
SET @.Params = N' @.AddressStreet1 VARCHAR(50)'
EXEC sp_executesql @.sSQL, @.Params, @.AddressStreet1 = @.PropStreet1
I'll see if I can clean this little box up enough to get Full-Text Search in
stalled (it really needs to be re-formatted... ah well...)
Thx
Mike C.