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 2005 Forums
 .NET Inside SQL Server (2005)
 Must declare the table variable

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
Go to Top of Page

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();
Go to Top of Page

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!!
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -