InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 16:44:26 GMT20http://www.sqlservercentral.com/Forums/Topic817615-8-1.aspx[quote][b]bjvaishnani (11/11/2009)[/b][hr]DECLARE @SelectQuery NVARCHAR(MAX)DECLARE @WhereQuery NVARCHAR(MAX)SET @WhereQuery = ' WHERE COND1 and cond2'SET @SelectQuery = ' SELECT Col1, col2,, From Table1 ' + @WhereQuerySET @CountSQLQuery = N'SELECT COUNT(*) FROM dbo.Table1 ' + @WhereQuerySET @TempPara = '@recordcount INT OUTPUT 'execute sp_executesql @SQLQuery, @TempPara, @recordcount = @CountSQLQuery----------------------------------------------------As i am geting the err.---> Error converting data type nvarchar(max) to int.When i try to run the sp...[/quote][code]SET @SQLQuery = ' SELECT Col1, col2,, From Table1 ' + @WhereQuery + ' SELECT @RCount = @@ROWCOUNT '.....execute sp_executesql @SQLQuery, @TempPara, @recordcount = @RCount OUTPUT[/code]Mon, 16 Nov 2009 16:25:11 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic817615-8-1.aspx[quote][b]DougGifford (11/13/2009)[/b][hr][quote][b]Markus Jägerskogh (11/12/2009)[/b][hr]If you need to do it by dynamic code you are boulding the parameters for [i]sp_executesql[/i] incorrectly.Short example:[code="sql"]DECLARE @SQLString nvarchar(500);DECLARE @ParmDefinition nvarchar(500);DECLARE @CountSQLQuery varchar(30);SET @SQLString = N'SELECT @result = COUNT(*) FROM INFORMATION_SCHEMA.TABLES';SET @ParmDefinition = N'@result varchar(30) OUTPUT';EXECUTE sp_executesql @SQLString, @ParmDefinition, @result=@CountSQLQuery OUTPUT;SELECT CAST(@CountSQLQuery as int);[/code]Good luck!/Markus[/quote]Question: Why "N'SELECT @result = COUNT(*) FROM INFORMATION_SCHEMA.TABLES' " and not" SELECT @result = COUNT(*) FROM INFORMATION_SCHEMA.TABLES' "What is the N' that preceeds the SELECT ?[/quote]Sorry... found my answer at:http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.htmlHas to do with Unicode strings and some stored proceedures like sq_executeSQL.Fri, 13 Nov 2009 10:21:31 GMTDougGiffordhttp://www.sqlservercentral.com/Forums/Topic817615-8-1.aspxThe N signifies unicode and keeps a conversion from happening. sp_executesql expects nvarchar (Unicode) parameters so when assigning values to them you should use N'string'Fri, 13 Nov 2009 10:21:29 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic817615-8-1.aspx[quote][b]Markus Jägerskogh (11/12/2009)[/b][hr]If you need to do it by dynamic code you are boulding the parameters for [i]sp_executesql[/i] incorrectly.Short example:[code="sql"]DECLARE @SQLString nvarchar(500);DECLARE @ParmDefinition nvarchar(500);DECLARE @CountSQLQuery varchar(30);SET @SQLString = N'SELECT @result = COUNT(*) FROM INFORMATION_SCHEMA.TABLES';SET @ParmDefinition = N'@result varchar(30) OUTPUT';EXECUTE sp_executesql @SQLString, @ParmDefinition, @result=@CountSQLQuery OUTPUT;SELECT CAST(@CountSQLQuery as int);[/code]Good luck!/Markus[/quote]Question: Why "N'SELECT @result = COUNT(*) FROM INFORMATION_SCHEMA.TABLES' " and not" SELECT @result = COUNT(*) FROM INFORMATION_SCHEMA.TABLES' "What is the N' that preceeds the SELECT ?Fri, 13 Nov 2009 10:10:56 GMTDougGiffordhttp://www.sqlservercentral.com/Forums/Topic817615-8-1.aspxIf you need to do it by dynamic code you are boulding the parameters for [i]sp_executesql[/i] incorrectly.Short example:[code="sql"]DECLARE @SQLString nvarchar(500);DECLARE @ParmDefinition nvarchar(500);DECLARE @CountSQLQuery varchar(30);SET @SQLString = N'SELECT @result = COUNT(*) FROM INFORMATION_SCHEMA.TABLES';SET @ParmDefinition = N'@result varchar(30) OUTPUT';EXECUTE sp_executesql @SQLString, @ParmDefinition, @result=@CountSQLQuery OUTPUT;SELECT CAST(@CountSQLQuery as int);[/code]Good luck!/MarkusThu, 12 Nov 2009 07:17:07 GMTHunterwoodhttp://www.sqlservercentral.com/Forums/Topic817615-8-1.aspxI think you need your @COuntSQLQUery to be this: "Select @recordcount = count(*) from dbo.table1"The question now becomes, why are you using dynamic sql for this?Thu, 12 Nov 2009 06:43:23 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic817615-8-1.aspxDECLARE @SelectQuery NVARCHAR(MAX)DECLARE @WhereQuery NVARCHAR(MAX)SET @WhereQuery = ' WHERE COND1 and cond2'SET @SelectQuery = ' SELECT Col1, col2,, From Table1 ' + @WhereQuerySET @CountSQLQuery = N'SELECT COUNT(*) FROM dbo.Table1 ' + @WhereQuerySET @TempPara = '@recordcount INT OUTPUT 'execute sp_executesql @SQLQuery, @TempPara, @recordcount = @CountSQLQuery----------------------------------------------------As i am geting the err.---> Error converting data type nvarchar(max) to int.When i try to run the sp...Wed, 11 Nov 2009 23:11:44 GMTbjvaishnani