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.
| Author |
Topic |
|
voyager838
Yak Posting Veteran
90 Posts |
 Posted - 2009-04-07 : 11:24:09
|
| Hi does anyone know what to do here?I creating a ServerProject in visualstudio.And try to send parameters to my stored procedurethat i want to deploy to my database.Now, when i doing this and sending that the parameterwould be fit as an tablelike this [Microsoft.SqlServer.Server.SqlProcedure()] public static void StoredProcedure2( SqlString table1) { // Put your code here SqlCommand myCommand = new SqlCommand(); SqlParameter sqlp2 = new SqlParameter("@tabl", SqlDbType.NVarChar); sqlp2.Value = table1; myCommand.Parameters.Add(sqlp2); myCommand.CommandText = @"SELECT * FROM @tabl"; // <-- it dosent like this! SqlContext.Pipe.ExecuteAndSend(myCommand); }I got the trouble Must declare the table variable "@tabl".A .NET Framework error occurred during execution of user-defined routine or aggregate "StoredProcedure2": System.Data.SqlClient.SqlException: Must declare the table variable "@tabl".System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteToPipe(SmiContext pipeContext) at Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command) at StoredProcedures.StoredProcedure2(SqlString table1)Anyone got an idea how to solve this.Yes when i use parametern in the conditions it works fine, but not when it being used as an table.Most grateful |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-04-07 : 14:14:10
|
Hello;You need to declare the variable @Tabl in order to use it as suggested by the error.DECLARE @Tabl varchar(50) for example...quote:
Yes when i use parametern in the conditions it works fine, but not when it being used as an table.
SqlParameter sqlp2 = new SqlParameter("@tabl", SqlDbType.NVarChar);It works here because @tabl is already defined inside SqlParameter myCommand.CommandText =@"SELECT * FROM @tabl"; // <-- it dosent like this!It does not work here because @tabl is not defined.What I am referring to is called 'scope'.Glad to help you with your homework assignment. At least you posted some code showing effort..r&r |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2009-04-07 : 14:52:17
|
Not sure why you would want to use a CLR procedure to do a select from a table. You will incur marshalling costs for all that data for no benefit, not to mention the wrath of database purists who consider the presence of a CLR assembly in SQL server as heresy.As revdnrdy pointed out, the way you have written it, you are creating a dynamic sql statement which reads SELECT * FROM @tabl. But the server has no idea what @tabl is.Instead, you can use something like:myCommand.CommandText = @"select * from " + table1.ToString(); |
|
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2009-04-08 : 08:56:58
|
| Thanks you both for your helps.revdnrdy: Its not a homework, its a part of my project,thanks for your considersunitabeck: Thanks that was really helpfull here, now it works.I don't know why i didn't remember to test that from beginning i should have, it has help me before, and so it did now.thanks!! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-04-08 : 09:47:49
|
| Also read this to understand why your method failedwww.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Must declare the table variable