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 procedure
that i want to deploy to my database.
Now, when i doing this and sending that the parameter
would be fit as an table
like 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 consider
sunitabeck: 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 failed
www.sommarskog.se/dynamic_sql.html
Madhivanan
Failing to plan is Planning to fail |
|
|
|
|
|
|
Must declare the table variable