Creating a table from .NET

I need to create tables from a C# code. I made a stored procedure hoping that I will pass a table name as a parameter: @.tabName. The procedure executed in SqlServer All Right but when I called it from C# code it created not the table I put in as a parameter but table "tabName." Otherwise everything else was perfect: coumns, etc.

Here is my stored procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateTableTick]
@.tabName varchar(24) = 0
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE tabName
(
bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)
END

What went wrong?

Thank you.

DECLARE @.SQL VARCHAR(500)

SET @.SQL = 'CREATE TABLE ' + @.TableName + ' (bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)'

EXEC @.Sql

-- It should be noted that you SHOULD SANITIZE your parameter and make sure that a user does not put anything non-alphanumeric and _ because a malicious user could potentially execute an sql injection if you did not.

If you search for sp_execsql I believe you will find tons of postings.

|||

To be honest, I wouldn't expect the SProc you've listed to work the way you've described in SQL Server either. What you're looking for is dynamic SQL, which has been discussed a number of times in the past weeks in this forum. I suggest you read the information at http://www.sommarskog.se/dynamic_sql.html before you proceed to ensure that you understand the security implications of using SPs with dynamic SQL before implement it.

If the possibility of SQL Injection is not an issue for you, or you've figure out how to mediate it, that same document also has some recomendations on possible implementations.

Mike

|||Thank you both, marcD and Mike.|||Hi,

you should better use the SMO classes which expose an interface (.NET API) for a developer to manage SQL Server objects. You don′t need to care about syntax or semantics, as SMO is object oriented and can be easily used within C# and Visual Studio (with Intellisense) to produce a SQL-injectionfree code (if used the right way :-) ) The API is the successor of the DMO classes, formerly used in SQL Server 2000 and below.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de