Yes, that ok
Why whit sp_executesql is not good ??
DECLARE @pConvID as integer
DECLARE @pclauseID as integer
Set @pConvID = 229
Set @pclauseID = 1
DECLARE @cpt as integer
DECLARE @SQL as nvarchar(1000)
DECLARE @pClauseTableName as varchar(50)
SELECT @pClauseTableName = Ltrim(Rtrim(cTableName)) FROM tblRefLstClauses WHERE iClauseID=@pClauseID
CREATE TABLE #myTable99 (Col1 int)
SELECT @SQL = 'INSERT INTO #myTable99 (Col1) Select Count(*) FROM '
+ ' WHERE '
+ '_est_analyse = '
+ '''' + 'non' + ''''
Originally posted by ericjean
Yes, that ok
Why with sp_executesql is not good ??
Because you can't assign a variable in that manner
Think of sp_executesql as an independant thread that does not "live" inside the current transaction. It's wrapped around by it, and will wait for it to complete, but it's "outside" of your current spid (I think)
Anyone with a better explination (esp if I'm waaaaaay off base)
I have to pipe in here. Even though I avoid using dynamic sql whenever possible, I think it is often faster than using a lot of conditional logic in your procedure or SQL statement. The two big beefs with dynamic sql is that it is hard to debug, but mostly that it cannot be precompiled. But when a SQL statement is loaded up with case statements and other conditional logic, often the existing query plan is not appropriate for many of the parameters submitted. I have seen certain cases where dynamic sql runs much faster than direct sql.
The dark side? I don't think so, but it's definitely on the gray side...
actually, there is a bigger issue with d-sql, - your security goes out the door. with d-sql you have to explicitly give all needed permissions to everybody who will execute your stored procedure. that's a double whammy: stored procedure + the table that your sp does d-sql against.
i agree with Brett, avoid it. instead you can do your average against all columns, put it into a temp table, and then make an assignment to your output parameter based on the value of the passed argument.
i just don't understand why there is so much negativity against temporary tables while trying to execute a dynamic sql?
True. I would not recommend concatenating input parameters into dynamic SQL statements for the security reasons you specify. I'm talking about constructing the statements based upon evaluation of the input parameters.