sql server sp_executesql error with sql string

Question

I have a string that looks like this:

set @sqlstring = N'select @mindate = min(time), @maxdate = max(time) from ' + @st_churn_active_table;

I print it and it looks like this:

select @mindate = min(time), @maxdate = max(time) from derp.derp_table

I run sp_executesql with parameter definitions like this:

execute sp_executesql @sqlstring, N'@maxdate date,@mindate date'

It errors like this:

The parameterized query '(@maxdate date,@mindate date)select @mindate = min(time), @maxda' expects the parameter '@maxdate', which was not supplied.

String becomes
'(@maxdate date,@mindate date)select @mindate = min(time), @maxda'
The sql string is cut off, does anyone know why and how to fix this?

Thanks!

Joe Stefanelli · Accepted Answer · 2011-10-04 17:55:10Z

2

See: How to specify output parameters when you use the sp_executesql stored procedure in SQL Server

answered Oct 4, 2011 at 17:55

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joel Coehoorn · Accepted Answer · 2011-10-04 18:00:41Z

First of all, sql server saw the whole string. It's only the error message that truncates it. Notice the error got the name of the missing parameter right, even though the truncated sql string only had part of the name.

Second, this isn't going to work how you want it to. You can't use output parameters that way with sp_executesql.

But finally to the problem of why it's throwing an error. I can't be sure, but I suspect the problem here is that there is a type mismatch, and so it can't use the parameter you gave it. I would hope that sql server would throw a better error message (complain about a type mismatch) in that situation, but I don't have sql server handy to test with and I can't think of any other reason for this to cause a problem.

Collectives™ on Stack Overflow

sql server sp_executesql error with sql string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related