Create table?

Hi All
I want to create a table with a name as a varible. That variable contains the table name by doing some string operations.
say: create table @.var
@.var contains the table name that is generated.
how could i do this?
plz help me .You will need to use dynamic sql to build your CREATE TABLE command, and then EXEC your dynamic sql.|||Why on earth would you ever want to do that? You are opening yourself up for a complete world of hurt. Whatever you are hoping to accomplish with this, you are most certainly solving the problem completely wrong.
Instead of asking fora horrible hack, you need to ask for advise on how to come up with an effective solution.|||Thanks a lot......... it solved my query.

Create Table with variable name

This should be simple, but...

I want to create a table in a stored proc using a variable name instead of something hard coded. I was hoping to do something like....

CREATE PROCEDURE foo

-- Add the parameters for the stored procedure here

@.TableName char = null

AS

BEGIN

SET NOCOUNT ON;

CREATE TABLE @.TableName (

[HRMONTH] [int] NULL,

[HRYEAR] [int] NULL

) ON [PRIMARY]

But no combination of names '@.'s, etc, allows me to use a variable name that I passed into the procedure. What am I missing? I will either receive a syntax error or the procedure will create a table called TableName rather than whatever TableName really stands for...

Thanks,

Tom

DECLARE @.ExecSQL NVARCHAR(300
SET @.ExecSQL = "CREATE TABLE @.TableName ..."
EXECUTE @.ExecSQL @.TableName

Remember that all variables have to be NVARCHAR and not VARCHAR. Also the exact syntax might be a bit off. In hat case use this as a reference. Hope this helps.|||

>>Remember that all variables have to be NVARCHAR and not VARCHAR

This is only true for sp_executesql, exec dynamic sql works with varchar also take a look at this example

declare @.table varchar(49),@.sql varchar(500)

select @.table ='Orders2006'
select @.sql = 'create table ' + @.table + '(id int)'
exec (@.sql)

exec('insert ' + @.table + ' values(1)')

exec('select * from ' + @.table)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Ahh. One thing to note about the above method is that I believe it may allows for more potentials for sql injections - may not be an issue with this but with queries and etc I believe it shoudl be avoided as opposed to the other method due to these security restrictions related to sql injection/execution.|||

There is always this

The Curse and Blessings of Dynamic SQL

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

It deals with the whole thing, injections, permissions etc etc

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks for the responses. This worked well, until I read the article in the previous post. So, maybe this wasn't such a hot idea...

Thanks again,

Tom

|||

One way is to take below approach which doesn't require dynamic SQL:

create table _tmp (

...

)

exec sp_rename _tmp, @.name_passed_to_proc

Create Login within a stored procedure

I want to create a login from within a stored procedure; passing in a
User name and a password.
It keeps giving me an error when I use a variable as the password.
what am I doing wrong?
CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]One hint.
Square brackets have the same effect as single quotes.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Jayme" <jayloub@.comcast.net> wrote in message
news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...
>I want to create a login from within a stored procedure; passing in a
> User name and a password.
> It keeps giving me an error when I use a variable as the password.
> what am I doing wrong?
>
> CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]
>|||Unfortunately, CREATE LOGIN doesn't accept parameters so you need to use
dynamic SQL. The example uses QUOTENAME to properly enclose the values and
handle embedded quotes.
CREATE PROC dbo.usp_CreateLogin
@.Login sysname,
@.Password sysname
AS
DECLARE @.CreateLoginStatement nvarchar(200)
SET @.CreateLoginStatement = 'CREATE LOGIN ' +
QUOTENAME(@.Login) +
' WITH PASSWORD = ' +
QUOTENAME(@.Password, '''')
EXEC sp_executesql @.CreateLoginStatement
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jayme" <jayloub@.comcast.net> wrote in message
news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...
>I want to create a login from within a stored procedure; passing in a
> User name and a password.
> It keeps giving me an error when I use a variable as the password.
> what am I doing wrong?
>
> CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]
>|||Thanks, that worked.
Dan Guzman wrote:[vbcol=seagreen]
> Unfortunately, CREATE LOGIN doesn't accept parameters so you need to use
> dynamic SQL. The example uses QUOTENAME to properly enclose the values an
d
> handle embedded quotes.
> CREATE PROC dbo.usp_CreateLogin
> @.Login sysname,
> @.Password sysname
> AS
> DECLARE @.CreateLoginStatement nvarchar(200)
> SET @.CreateLoginStatement = 'CREATE LOGIN ' +
> QUOTENAME(@.Login) +
> ' WITH PASSWORD = ' +
> QUOTENAME(@.Password, '''')
> EXEC sp_executesql @.CreateLoginStatement
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jayme" <jayloub@.comcast.net> wrote in message
> news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...

Create Login within a stored procedure

I want to create a login from within a stored procedure; passing in a
User name and a password.
It keeps giving me an error when I use a variable as the password.
what am I doing wrong?
CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]One hint.
Square brackets have the same effect as single quotes.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Jayme" <jayloub@.comcast.net> wrote in message
news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...
>I want to create a login from within a stored procedure; passing in a
> User name and a password.
> It keeps giving me an error when I use a variable as the password.
> what am I doing wrong?
>
> CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]
>|||Unfortunately, CREATE LOGIN doesn't accept parameters so you need to use
dynamic SQL. The example uses QUOTENAME to properly enclose the values and
handle embedded quotes.
CREATE PROC dbo.usp_CreateLogin
@.Login sysname,
@.Password sysname
AS
DECLARE @.CreateLoginStatement nvarchar(200)
SET @.CreateLoginStatement = 'CREATE LOGIN ' +
QUOTENAME(@.Login) +
' WITH PASSWORD = ' +
QUOTENAME(@.Password, '''')
EXEC sp_executesql @.CreateLoginStatement
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jayme" <jayloub@.comcast.net> wrote in message
news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...
>I want to create a login from within a stored procedure; passing in a
> User name and a password.
> It keeps giving me an error when I use a variable as the password.
> what am I doing wrong?
>
> CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]
>|||Thanks, that worked.
Dan Guzman wrote:
> Unfortunately, CREATE LOGIN doesn't accept parameters so you need to use
> dynamic SQL. The example uses QUOTENAME to properly enclose the values and
> handle embedded quotes.
> CREATE PROC dbo.usp_CreateLogin
> @.Login sysname,
> @.Password sysname
> AS
> DECLARE @.CreateLoginStatement nvarchar(200)
> SET @.CreateLoginStatement = 'CREATE LOGIN ' +
> QUOTENAME(@.Login) +
> ' WITH PASSWORD = ' +
> QUOTENAME(@.Password, '''')
> EXEC sp_executesql @.CreateLoginStatement
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jayme" <jayloub@.comcast.net> wrote in message
> news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...
> >I want to create a login from within a stored procedure; passing in a
> > User name and a password.
> > It keeps giving me an error when I use a variable as the password.
> > what am I doing wrong?
> >
> >
> > CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]
> >

Create Global Variable like VB... can it be done?

I just want to store the value of a parameter in a global variable that all my reports in the same project can use.

My goal is to create a dynamic query. For example:

Company Name: Widgets inc.

Divisions: Sales, Service, Tech, Accounting

I have a matrix and when I click on the more information button it goes to another report. I want the next report to know what division is currently selected in the dropdown parameter. So, being a VB programmer, I thought I could store parameter1.division.value into a global variable and update the variable whenever the parameter changes.

This way, on the next report, my query's where statement is the global variable.

@.GlobalVariable = parameter1.division.value

Select name, address, phone FROM Employee WHERE division = @.GlobalVariable

I am using Visual Studio to design this project although I would prefer to use VB or ASP. But this is my only stumbling block right now. Everything else is complete.

Please let me know if anyone can help.

Thanks.

John

There is not a concept of a global variable for multiple reports that I know of.

You could potentially do something with a table that could store the global value by user id & identifier, and retrieve the variable from the table. Or you could setup a web service to get/set the variable.

You may be able to add a reference to a DLL in every report, and share between them, though I woud think it would be destroyed after the report session times out.

http://www.codeproject.com/dll/data_seg_share.asp

cheers,

Andrew

|||

Looks like you need a report parameter. You don't need to show it to the user, but just default it.

create function

I want to assign the querie to the ptvalue variable... i just want which mistake im making, it trow me an error near to the select.

create function pt
(@.idpt INT)
Returns INT
AS
BEGIN
declare @.ptvalue INT
SET @.ptvalue = select sqrt(a.px*a.px + a.py*a.py)
from abstractparticle as a
where @.idpt = a.id;
Return @.ptvalue
END

It looks to me like if you enclose your select statement with parends that your query will work.

create function pt
(@.idpt INT)
Returns INT
AS
BEGIN
declare @.ptvalue INT
SET @.ptvalue = (select sqrt(a.px*a.px + a.py*a.py)
from abstractparticle as a
where @.idpt = a.id);
Return @.ptvalue
END

I will give this a look in a sec for verification.

|||

Yes, the parends will fix the define; you can also define your function without the variable if you choose; something like this:

create function pt
(@.idpt INT)
Returns INT
AS
BEGIN
Return ( select sqrt(a.px*a.px + a.py*a.py)
from abstractparticle as a
where @.idpt = a.id)
END

|||

Another thing to consider is that the fact that this is a scalar function. Referencing this function will likely make your code more readable; however, keep in mind that scalar functions tend to have a negative impact on performance.

If you have queries that use this function that seem to perform badly one way to improve performance at the expense of readability will always be to replace the UDF function references with the SELECT statement that you have enclosed in parends.

CREATE DATABASE script doesnt accept a variable for a FILENAME

I want to create a SP that creates a new database, so I script-ed out the db and paste the script into new SP gui (SQL 2000). I want to pass it a variable for the data/log file location that is not the default location. The original script looks like this:

CREATE DATABASE [PWRR_DDS] ON (NAME = N'PWRR_DDS_Data', FILENAME = N'F:\SQL SERVER FILES\Databases\dbName.mdf' , SIZE = 3118, FILEGROWTH = 10%) LOG ON (NAME = N'PWRR_DDS_Log', FILENAME = N'F:\SQL SERVER FILES\Databases\dbName_Log.LDF' , SIZE = 5000, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS.

I replaced the path of the FILENAME variable like this:

CREATE DATABASE [PWRR_DDS] ON (NAME = N'PWRR_DDS_Data', FILENAME = @.DBPath, SIZE = 5000, FILEGROWTH = 10%) LOG ON (NAME = N'PWRR_DDS_Log', FILENAME = @.LogPath , SIZE = 5000, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS,

declaring the variables as char(500). The error I get is "Incorrect sybtax near'@.DBPath'.

Any ideas for workaround?

Thanks,

EJM

You could build the stmt and EXEC it.

declare @.sqlnvarchar(500)set @.sql ='CREATE DATABASE [' + @.dbname + +'] ON (NAME = ' + @.datafile +', FILENAME = ' + @.DBPath +', SIZE = 5000, FILEGROWTH = 10%) LOG ON (NAME = ' + @.log +', FILENAME = ' + @.LogPath +', SIZE = 5000, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS..'EXEC(@.sql)