Dynamic SQL help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL help needed

Author

Topic

ghound
Starting Member

3 Posts

Posted - 2007-02-22 : 12:37:09

Hi there

I'm having some problems with a parameterized dynamic sql query.

I am using ADO.NET to set up the query and use parameters to specify the table name and where clause. The SQL that I can see hitting the server is as follows (line breaks added for readability):


declare @P1 int
set @P1=NULL
  exec sp_prepexec @P1 output, N'@DBL varchar(100),
@CID int', N'SELECT PTAID, PA_Value FROM @DBL WHERE CID=@CID', 
@DBL = 'DCMS2_Live.dbo.Data_ProductAttribute', @CID = 1787
select @P1

This results in the following error message:


Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@DBL'.
Server: Msg 8180, Level 16, State 1, Procedure sp_prepexec, Line 3
Statement(s) could not be prepared.

(1 row(s) affected)

If it's any help, the c# code is as follows:


string strSqlCmd;
			DataSet ds = new DataSet(), lds = new DataSet();
			SqlCommand sqlCmd = new SqlCommand();
			SqlDataAdapter mySqlDataAdapter;

			strSqlCmd="SELECT PTAID, PA_Value FROM @DBL WHERE CID=@CID";
			sqlCmd = new SqlCommand(strSqlCmd, connDBConnection, tran);
			sqlCmd.CommandType=CommandType.Text;
			sqlCmd.Parameters.Add("@DBL", SqlDbType.VarChar, 100);
			sqlCmd.Parameters["@DBL"].Value=strDBLiveName+".dbo.Data_ProductAttribute";
			sqlCmd.Parameters.Add("@CID",SqlDbType.Int).Value=CID;

			sqlCmd.Prepare();

			mySqlDataAdapter = new SqlDataAdapter(sqlCmd);
			mySqlDataAdapter.Fill(lds);

I'm really tearing my hair out on this one and any pointers as to where I am going wrong would be very much appreciated!

Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-22 : 13:08:46

I can't understand why you are adding table name as parameter.

1. This is not a good practice as well as you don't have good DB design
2. Why not just simply use @DBL while building query text:

strSqlCmd="SELECT PTAID, PA_Value FROM " + strDBLiveName + ".dbo.Data_ProductAttribute WHERE CID=@CID";

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-22 : 13:09:59

http://www.sommarskog.se/dynamic_sql.html

www.elsasoft.org

ghound
Starting Member

3 Posts

Posted - 2007-02-23 : 04:19:46

quote:
Originally posted by harsh_athalye

I can't understand why you are adding table name as parameter.

1. This is not a good practice as well as you don't have good DB design
2. Why not just simply use @DBL while building query text:

strSqlCmd="SELECT PTAID, PA_Value FROM " + strDBLiveName + ".dbo.Data_ProductAttribute WHERE CID=@CID";

Harsh Athalye
India.

"The IMPOSSIBLE is often UNTRIED"

Hi there

Thanks for your comments. The database design is incredibly hideous (I have been working with SQL as a database admin and an application developer for 7 years and I have never seen a more illogical design choice). There are basically 3 copies of the same database, a live, archive and design version, and several sql statements executed by the application that need to modify the data in multiple databases at once. I realize that a better situation is to modify the database schema so that everything sits in one db, but that is not possible at this time for a multitude of reasons.

I am simply trying to optimize the old dog (polishing a **** is the phrase that immediately springs to mind) and I have managed to make some queries perform between 10 and 70x faster by using parameterized dynamic sql over ah-hoc SQL created in-program with string concatenation, with the aid of the SQLCommand.Prepare().

I have very little experience of dynamic sql as I have always preferred using SPs, but in this case I was hoping that it may be the solution.

Thanks for your help.

ghound
Starting Member

3 Posts

Posted - 2007-02-23 : 04:34:12

quote:
Originally posted by jezemine

http://www.sommarskog.se/dynamic_sql.html

www.elsasoft.org

Thanks - this is a very useful reference.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources