SQL Server Performance Forum – Threads Archive

execute dynamic sql statement

I am trying to run a dynamic sql statement but the statment is too long to fit into varchar(4000). When I declare @strSQL to varchar(5000), it tells me that the max characters for varchar is 4000. What data type can I use to hold my synamic sql statement? Help Thanks Bugme

---

Are you using SQL 2005 or SQL 2000? Since you are posting this in sql 2005 forum, you can use varchar(max)in sql server 2005. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

You can execute dynamic sql by Exec or sp_executesql. sp_executesql is always recommended and it can have only From the below mentioned site The first parameter @stmt is mandatory, and contains a batch of one or more SQL statements. The data type of @stmt is ntext in SQL 7 and SQL 2000, and nvarchar(MAX) in SQL 2005. Beware that you must pass an nvarchar/ntext value (that is, a Unicode value). A varchar value won’t do. Refer :http://www.sommarskog.se/dynamic_sql.html Madhu

---

It sounds like you are using sp_executesql and your variable is declare as nvarchar(4000) and not varchar(4000). sp_executesql required unicode text. So you must have declared it as nvarchar. The max length for varchar is 8000 so nvarchar is 4000.
KH

---

I got it to work by breaking down the statement into 3 parts. Thanks for all the help.

---

For executing dynamic SQL you must have to declare the variable as nvarchar.
do it. i think it will work fine Regards
Sudipta K. Ghosh

---

As Dilli Grg has already asked you, which sql server version you are using, If it is 2005, then you dont even need to break down the statement.

---

]]>