Stored Procedure : Create Table With Parameter ?

Jul 28, 2006



first of all hi all, my problem is, i want to create a table which name from my parameter

what should i do ?

here my code

CREATE PROCEDURE ProcedureName

-- Add the parameters for the stored procedure here

@ype nvarchar(15)

AS

BEGIN

SET NOCOUNT ON;

CREATE TABLE [dbo].[@ype](

[TeklifEM] [bigint] NOT NULL,

[Cinsi] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Adet] [int] NULL,

[Birim] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BirimFiyat] [money] NULL,

[ToplamFiyat] [money] NULL,

[TeklifId] [bigint] NULL,

CONSTRAINT [PK_ype] PRIMARY KEY CLUSTERED

(

[TeklifEM] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]



END

GO

View 3 Replies


ADVERTISEMENT

How To Create Rdl Using Stored Procedure Which Uses Parameter

Apr 3, 2008

Hi
I am sql server 2005.
how to create rdl using stored procedure which uses parameter

Thank you.

View 1 Replies View Related

Can I Create A 'Top N' Statement Within A Stored Procedure Using A Parameter?

Jul 20, 2005

In a 'Top n' type statement I wish to be able to insert the n valuefrom a parameter, within a stored precedure egHaving declared @pageSize as a parameter I want to run the followingtype of query :SELECT DISTINCT TOP @pageSize routeID, routeName FROMtblRoute_HeaderWhen I attempt to do so I get an error mesage indicating incorrectsyntax. I do not get an error message if I specify 'n' directly as inTOP 10Am I missing something or is this not possible within a storedprocedure?Best wishes, John Morgan

View 5 Replies View Related

T-SQL (SS2K8) :: Procedure That Create Views With Table Name And A Table Field Parameter?

Aug 4, 2015

I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).

However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.

Below code.

ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

[code]....

View 9 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

Table Parameter In Stored Procedure?

Jun 2, 2012

I am Creating a Procedure in SQL2000 but founding an error.

Procedure:
CREATE PROCEDURE Itmopbalance
@myfitemcode int,
@ttype char(1),
@myfyear char(9),
@mydatefrom2 datetime,

[code]....

View 3 Replies View Related

Is It Possible To Use Table Output Parameter Of Stored Procedure? If Possible, How?

Jun 28, 2007

is it possible to use table output parameter of stored procedure? if possible, how?
 
thanks

View 2 Replies View Related

STORED PROCEDURE - Passing Table Name As A Parameter

Nov 29, 2005

I am trying to develop a stored procedure for an existing application thathas data stored in numerous tables, each with the same set of columns. Themain columns are Time and Value. There are literally hundreds of thesetables that are storing values at one minute intervals. I need to calculatethe value at the end of the current hour for any table. I am a little newto SQL Server, but I have some experience with other RDBMS.I get an error from SQL Server, complaining about needing to declare@TableName in the body of the procedure. Is there a better way to referencea table?SteveHere is the SQL for creating the procedure:IF EXISTS(SELECTROUTINE_NAMEFROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_TYPE='PROCEDURE'ANDROUTINE_NAME='udp_End_of_Hour_Estimate')DROP PROCEDURE udp_End_of_Hour_EstimateGOCREATE PROCEDURE udp_End_of_Hour_Estimate@TableName VarCharASDECLARE @CurrentTime DateTimeSET @CurrentTime = GetDate()SELECT(SELECTSum(Value)* DatePart(mi,@CurrentTime)/60 AS EmissonsFROM@TableNameWHERETime BETWEENDateAdd(mi,-DatePart(mi,@CurrentTime),@CurrentTime)AND@CurrentTime)+(SELECTAvg(Value)* (60-DatePart(mi,@CurrentTime))/60 AS EmissionsFROM@TableNameWHERETime BETWEENDateAdd(mi,-10,@CurrentTime)AND@CurrentTime)

View 15 Replies View Related

Stored Procedure - Insert With Table Name From Parameter

Jul 31, 2006

First of all hi for all,

I m trying to make insert stored procedure with parameters, i want to send table name and insert values with parameters, i m trying that below stored procedure but it gives syntax eror to me what shoul i do to correct it ?



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[insertalepform]

-- Add the parameters for the stored procedure here

@type nvarchar(15), @talepbrmno int, @birimisteksayi bigint, @birimistektarih datetime,

@aciklama nvarchar(50), @onay int, @seviye int, @talepformno bigint, @taleptarih datetime

AS

BEGIN

SET NOCOUNT ON;

exec ('INSERT INTO [BelediyeWork].[dbo].['+@type+'TalepFormu]

([TalepBirimNo], [BirimistekSayısı], [BirimistekTarihi], [Acıklama], [Onay]

,[Seviye], [TalepFormNo], [TalepTarih])

VALUES ('+@talepbrmno+','+@birimisteksayi+','+@birimistektarih+','+@aciklama+'

,'+@onay+','+@seviye+','+@talepformno+','+@taleptarih+') ')

END

View 12 Replies View Related

Need To Create Table With Stored Procedure

Feb 10, 2005

I need to use a stored procedure that will create a table. The table name must be passed to the stored procedure.
This is what I have so far, but it does not allow me to run it.

alter procedure dbo.createNewBUtable
(
@BU as varchar(50)
)
as
set nocount on;
create table @BU
(
BUid varchar(50) primary key,
BUinfo varchar(50)
)

View 4 Replies View Related

API To Create Table, Stored Procedure, Etc.

May 2, 2007

In SQL Manager I can right click on a stored procedure, table, etc. and I am presented with a list of options one of which is "Create >". Clicking this I can get a script that will create the respective stored procedure or table and write the create script to the clipboard, a file, or an SQL query window. I want to automate this to essentially selectively "back up" our data base by creating one large script that will create the tables and stored procedures from the database. Is there an API, tool, or library call that I can get access to the code that implements these menu selections?



Thank you.



Kevin

View 1 Replies View Related

Passing Table Name And Order By Parameter To Stored Procedure

Jul 27, 2007

can i pass the name of the table and the "order by" column name to stored procedure?
 i tried the simple way
(@tablename varchar and then "select * from @tablename)
but i get error massesges. the same for order by...
what is the right syntex for this task?

View 2 Replies View Related

How To Get 'Create Script' For A Table Using Stored Procedure?

Oct 20, 2005

How to get the Create Script for a table, using SQL Stored Procedure ?Table name is supplied and I want to build the Create script for that table.Pls let me know ASAP.Thanks in Advance.

View 2 Replies View Related

Create And Read A Table From A Stored Procedure

Nov 25, 2001

In a stored procedure, I am trying to create a table and then read it from within that SP or from another one (nestsed). When I run the below code (simplified version of my real SP), no records are returned even though I know that there is data. The table is created and records are inserted, I just cannot read the records from the SP or one that calls it with an EXEC.

Any ideas? My code is listed below. (When I get this to work, I plan to convert the code to manipulate a temporaty table).

Alter Procedure spWod_rptWoStatusSummary_9

As
BEGIN
CREATE TABLE tblStatusSummary (TckStaffCd_F Char(3), RecType Char(20))
INSERT INTO tblStatusSummary
SELECT TckStaffCd_F, RecType
END

BEGIN
Select * from tblStatusSummary
end

View 1 Replies View Related

Transact SQL :: How To Create A Table Using Stored Procedure

Sep 1, 2015

Need creating a stored procedure which creates a table.

View 3 Replies View Related

.NET Framework :: CLR Stored Procedure With Table Data Type Parameter

May 27, 2015

I want to create a CLR Stored Procedure with a Table User Defined Type like this short example in SQL:

CREATE TYPE [dbo].[TypeTable] AS TABLE
(
[Id] [integer] NOT NULL,
[Value] [sysname] NOT NULL,
PRIMARY KEY CLUSTERED

[Code] ....

I found some example in this link : CLR UDT

But I can't figure how to do the same with a User Defined Type Table.

View 5 Replies View Related

Create A String Of Records From A Table In A Stored Procedure,

Jul 20, 2005

I have a table tblCustomers in a one-to-many relationship with tabletblProducts.What I want to do is to create a stored procudure that returns a listof each customer in tblCustomers but also creates a field showing astring (separated by commas)of each matching record in tblProducts.So the return would look like:CustID Customer ProductList1 Smith Apples, Oranges, Pears2 Jones Pencils, Pens, Paperetc...Instead of:CustID Customer Product1 Smith Apples1 Smith Oranges1 Smith Pears2 Jones Pencils2 Jones Pens2 Jones PaperWhich is what you get with this:SELECT tblCusomers.CustID, tblCusomers.Customer,tblProducts.ProductFROMtblCusomers INNER JOINtblProducts ONtblCustomers.CustID = tblProducts.CustIDI'd appreciate any help!lq

View 6 Replies View Related

CREATE TABLE/VIEW From Stored Procedure Or SELECT...

Jun 8, 2006

Can anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?

I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:

CREATE TABLE < t > FROM SELECT id, name FROM < w > ...

Thank you!

View 6 Replies View Related

In Stored Procedure How To Loop Through Rows In Table And Pass Parameter To EXEC SP

Apr 26, 2008

I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?

View 7 Replies View Related

Create XML In ASP.NET 2.0 Then Use For Joined Table In SQL Server 2005 Stored Procedure

Jun 11, 2006

Here's my problem:I'm developing an ASP.NET 2.0 application that has a user select one or moreauto manufacturers from a listbox ("lstMakes"). Once they do this, anotherlistbox ("lstModels") should be filled with all matching models made by theselected manufacturers. If lstMakes was not multi-select, I'd have noproblem. But in this case it has to be multi-select. The database is SQLServer 2005 which does not accept arrays as parameters. I've been told thatI have to create an XML document that will act as a filtered Manufacturerstable that I can join to my Models table in my stored procedure. Problem isI don't have the foggiest idea how to do this. I've seen some examples thatjust leave me scratching my head so I was hoping someone could look at whatI'm trying to do and show me how to do this. Thanks!

View 2 Replies View Related

T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?

May 21, 2014

Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

RS 2005: Stored Procedure With Parameter It Runs In The Data Tab But The Report Parameter Is Not Passed To It

Feb 19, 2007

Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.

I have to display the result of a parameterized query and I created a SP that based in the parameter does something:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''

AS
BEGIN

declare @slqSelectQuery nvarchar(MAX);

SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end

Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.

Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried

the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!

What is wrong? What I forgot??

Thankx for any help!
Marina B.





View 3 Replies View Related

Stored Procedure With User!UserID As Parameter, As Report Parameter?

Jul 2, 2007

I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.

Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks

View 1 Replies View Related

Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied

Mar 26, 2007

Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}
 
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!
 
Thanks Nickdel68

View 5 Replies View Related

Reporting Services :: Procedure Or Function Create Session Expects Parameter Which Was Not Supplied

Jun 4, 2015

When i open any reports getting the below error message.An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.

(rsReportServerDatabaseError)Procedure or function 'CreateSession' expects parameter '@SiteZone', which was not supplied.  

View 7 Replies View Related

Transact SQL :: Using A Parameter To Create A Table?

Jul 21, 2015

I create a script like below:

GO
SET ANSI_PADDING ON
GO
DECLARE
@ProjectID  AS NVARCHAR(128),
@TableName  AS NVARCHAR(128);
SET @ProjectID = N'EPA';

[code]....

The table created, but T-SQL created a table called @TableName.not like "EPA_SweetChargeCodeAssignees'

how to debug?

View 2 Replies View Related

Stored Procedure Not Getting A Parameter

Feb 12, 2008

I have this update procedure that updates and encrypts a credit card column. I added the pass phrase to a table and now I'm trying to use that pass phrase in my update procedure.
When I run the update from the app the error is that the procedure is not getting the @Phrase parameter.
 Can anyone see why this might be?ALTER PROCEDURE [dbo].[UpdatePayment]

@paymentID int,
@PaymentDate datetime,
@TenderTypeID int,
@PaymentAmount money,
@CreditCardType int,
@ExpirationDate datetime,
@ccNumber nvarchar(50),
@Phrase nvarchar(25)

AS
BEGIN
SET NOCOUNT ON;
SET @Phrase = (SELECT Phrase FROM tblSpecialStuff)

UPDATE tblReceipts SET

PaymentDate=@PaymentDate,
TenderTypeID=@TenderTypeID,
AmountPaid=@PaymentAmount,
CreditCardType=@CreditCardType,
ExpirationDate=@ExpirationDate,
ccNumber = (CASE
WHEN @CreditCardType > 0 THEN
EncryptByPassPhrase(@Phrase,@ccNumber)
ELSE @ccNumber
END)
END 

View 1 Replies View Related

Using A Parameter In A Stored Procedure

Dec 23, 2003

hi,

i need to run a stored procedure from an asp page that will import data into a temporary table.

i am having trouble with passing the parameter from the Exec command (currently developing in QA). It works ok where i want to use a value such as 'PC', or 'printer' or 'laptop' where the import sql has a where clause of '... = <<parameter>>...'.

I want however to be able to pass it a value such as ['printer','laptop','pc']so that the procedure will collect the data into the temp table with an '...in <<parameter>>'

this falls over and retrieves zero rows. the parameter is passed to the sp correctly as i have used a print command to display it, it comes back as 'pc',printer','laptop'. It appears to be the ' in ' that is not parsing the parameter.

can anyone help please?

TIA

View 3 Replies View Related

Parameter To Stored Procedure

Sep 3, 1999

Hello!

I have a problem when i want to construct a stored procedure. I want to pass a parameter with customerid's. The problem is that I don´t know how many customerId's I send to the stored procedure, because the user select the customerId's from a selectbox. The SQL string I want to excute looks like this

SELECT CustomerName FROM Customer WHERE CustomerId IN (199, 301, 408... )

How should I create the stored procedure so I put the customerId values in a parameter, like the procedure below? (I know that this not will work)

CREATE PROCEDURE rpt_PM2000_test(@SCustomerId varChar)
AS
BEGIN
SELECT Customer
FROM Customer
WHERE CustomerId IN (@sCustomerId)
END

/Fredrik

View 3 Replies View Related

Stored Procedure Parameter

Apr 22, 2007

In the Create portion of a stored procedure, does the line in bold mean
that if the parameter @RegoDate is not provided, then use the current datetime?

CREATE PROCEDURE spGetRegoDetail
@Owner nvarchar(20),
@RegoDate datetime = getdate
AS ...

Thanks in advance,
G11DB

View 2 Replies View Related

Stored Procedure Parameter

Mar 4, 2004

Hi,

I have an Access front end/MSDE2000 backend system. There is a form that has a subform which is based on the results of a stored procedure. Both my subform and mainform contain intOrderID ( which is the field i want to link both of them with ).

I would like to have the subform's data updated when changing records in the mainform. In Access, this was simple ( link child and master fields ), but not with MSDE2000 as a back end ( ADO connection thing...)

I think i have to create something to filter the results of the stored procedure, but i dont like the fact the the entire dataset needs to be transmitted over the network ( most of the time, users will only be creating new records, or reviewing recently created ones )

I'm really not sure as to how to accomplish this... Any ideas?

thanks!

View 10 Replies View Related

Parameter For Stored Procedure

Jun 16, 2007

HiI'm trying to alter my stored procedure to take a parameter for theDatabase Name, but as usual the syntax is killing me.Thanks for any helpDennis'--------------------------------------------------------------------------*--------------------------------Before - This Works without a paramater'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE [MyClientDatabase] ON PRIMARY( NAME = N'MyClientDatabase',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'MyClientDatabase_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_AS'--------------------------------------------------------------------------*--------------------------------After - This Doesn't work with a parameter'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE @ClientDBName ON PRIMARY( NAME = N@ClientDBName,FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'@ClientDBName' + '_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_ASMsg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 4Incorrect syntax near '@ClientDBName'.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved