Creating New Table From Passed Variabe In MSSQL Stored Procedure

Mar 2, 2004

Hi there i really cant understand why this should be a problem... it seems that i cant create a table from a stored procedure when passing the tablenam from application like this...





CREATE PROCEDURE dbo.CreateTable


@TableName NVARCHAR(50)


AS


BEGIN





create table @TableName ([id] int identity(1,1),[diller] int)





end


GO





THIS CANT BE TRUE !!!!!!!!

View 4 Replies


ADVERTISEMENT

SQL Server 2012 :: Avoiding Temp Table With String Of IDs Passed Into Stored Procedure

Sep 26, 2014

Using a string of IDs passed into a stored procedure as a VARCHAR parameter ('1,2,100,1020,') in an IN without parsing the list to a temp table or table variable. Here's the situation, I've got a stored procedure that is called all the time. It's working with some larger tables (100+ Million rows). The procedure passes in as one of the variables a list of IDs for the large table. This list can have anywhere from 1 to ~100 IDs passed to it.

Currently, we are using a function to parse the list of IDs into a temp table then joining the temp table to get the query:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] .....

The problem we're running into is that since this proc gets called so often, we sometimes run into tempDB contention that slows this down. In my testing (unfortunately I don't have a good way of generating a production load) swapping the #table for an @table didn't make any difference which makes sense to me given that they are both allocated in the tempDB. One approach that I tried was that since the SELECT query is pretty simple, I moved it to dynamic SQL:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] ....

The problem I had there, is that it creates an Ad Hoc plan for the query and only reuses it if the same list of parameters are passed in, so I get a higher CPU cost because it compiles a plan and it also causes the plan cache to bloat since the parameter list is almost always different. Is there an approach that I haven't considered that may get the best of both worlds, avoiding or minimizing tempDB contention but also not having to compile a new plan every time the proc is run?

View 9 Replies View Related

Creating Stored Procedure That Inserts Value In Table

Feb 13, 2008

I am using vwde2008 and created db and table. i want to create a stored procedure that will insert values. I am bit lost on how write this.
this is my table info
table = BusinessInfo
Columns = BusinessID, BusinessName, BusinessAddress
how can i create a stored procedure ?

View 1 Replies View Related

Creating A Table Inside A Stored Procedure

Feb 26, 2004

I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:

Error 208: Invalid object name '##OPTIONSEX'

I am using the following SQL script:

CREATE PROCEDURE [dbo].[Test2] AS

CREATE TABLE ##OPTIONSEX
(
OPTION_PLAN VARCHAR(50),
TOT_OPTIONS_EXCHANGED FLOAT NULL
)

GO

INSERT ##OPTIONSEX

SELECT
B.COMPONENT,
TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B
WHERE B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

GO

Any help getting this to run correctly would be appreciated.

View 3 Replies View Related

ERROR: Too Many Arguments Passed To Stored Procedure

Jan 22, 2002

Hi,

We have an application written in ASP that calls a MS-SQL stored procedure and passes several parameters. Once in a while, we get this error, but most of the time it works. We can also run this in SQL query analyzer without ever a problem. We've looked at the obvious and found nothing wrong.

Please help! Any suggestions are appreciated.
(I posted this in ASP discussion board but no one replied)

Colleen

--------------------------------------------------------------------------------


|

View 1 Replies View Related

Optional Parameters Passed To Stored Procedure

Jul 23, 2005

In which system table the information about the optional parameters passed to stored procedure are stored.I know about the tbl_columns and all that stuff. From where can i can come to know the parameter is mandatory or optional.--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Creating A Table Through Stored Procedure Shows An Syntax Error

May 26, 2000

hai guys,
i have written a stored procedure which creates a table ex:
USE PUBS
GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'RC_STRPROC')
DROP PROCEDURE RC_STRPROC
GO
USE PUBS
GO
CREATE PROCEDURE RC_STRPROC
(@TBLNAME VARCHAR(35), @COLVAL1 VARCHAR(35), @COLVAL2 VARCHAR(35))
AS
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = '@TBLNAME')
DROP TABLE @TBLNAME
CREATE TABLE @TBLNAME
(@COLVAL1, @COLVAL2)
GO
it gives an syntax error at '@tblname'
can u guys tell me the problem

thanks
hiss

View 2 Replies View Related

Creating Stored Procedure With Temp Table - Pass 6 Parameters

Sep 9, 2014

I need to create a Stored Procedure in SQL server which passes 6 input parameters Eg:

ALTER procedure [dbo].[sp_extract_Missing_Price]
@DisplayStart datetime,
@yearStart datetime,
@quarterStart datetime,
@monthStart datetime,
@index int
as

Once I declare the attributes I need to create a Temp table and update the data in it. Creating temp table

Once I have created the Temp table following query I need to run

SELECT date FROM #tempTable
WHERE #temp.date NOT IN (SELECT date FROM mytable WHERE mytable.date IN (list-of-input-attributes) and index = @index)

The above query might return null result or a date .

In case null return output as "DataNotMissing"
In case not null return date and string as "Datamissing"

View 3 Replies View Related

Creating Stored Procedure To Use With Crystal Reports - Table Join

Dec 30, 2014

I am a newbie to SQL and I am trying to create a stored procedure to use with Crystal Reports.

I don't understand why I am having trouble joining tables to the Member table. These table joins have worked in the past.

Create Proc rpmb_FamilyPortraitDirectoryP1Test
(
@ChurchId int,
@StartFamilyName varchar(50),
@EndFamilyName varchar(50),
@MemberTypeId varchar(max) = Null,

[Code] ....

View 4 Replies View Related

Creating A Dynamic Global Temp Table Within A Stored Procedure

Sep 7, 2006

hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?

View 16 Replies View Related

Compare With Multiple Date Values Passed Into Stored Procedure?

Dec 14, 2012

I have a scenario where I need to compare a single DateTime field in a stored procedure against multiple values passed into the proc.So I was thinking I could pass in the DateTime values into the stored procedure with a User Defined Table type ... and then in the stored procedure I would need to run through that table of values and compare against the CreatedWhenUTC value.I could have the following queries for example:

WHERE CreatedWhenUTC <= dateValue1 OR CreatedWhenUTC <= dateValue2 OR CreatedWhenUTC <= dateValue 3

The <= is determined by another operator param passed in. So the query could also be:

WHERE CreatedWhenUTC > dateValue1 OR CreatedWhenUTC > dateValue2 OR CreateWhenUTC > dateValue3 OR CreateWhenUTC > dateValue4

View 3 Replies View Related

SQL Server 2012 :: Can Excel Be Passed As Stored Procedure Parameter

Sep 29, 2015

Currently i am using SQL Server 2012 Import/Export Wizard to upload data to sql tables manually. However i was trying to write a procedure to update that table. and on the time of execution, if i can pass excel. Is there any way to pass excel to stored procedure parameter?

View 1 Replies View Related

Transact SQL :: Quotes Around Retrieved Date To Be Passed As Parameter To Stored Procedure

May 4, 2015

I'm trying to replace a view with stored procedure for faster performance. the View is called by end user using a query as below, I need to pass the date as parameter for sp to execute with quotes for it to execute with correct results.  I tried to pass the date as parameter but could not execute stored procedure with correct results. Is there any way to put quotes around returned date from sub query : 

Execute statement is like
Exec dbo.storedProc1  select max(date) from table

I need to pass the above as  :

Exec dbo.storedProc '2015-03-24'  .

Somehow passing the date as parameter is giving me empty result set.

View 2 Replies View Related

SQL 2012 :: SSIS Passing Parameters To Stored Procedure That Changes Based On The Data Being Passed?

Jun 23, 2015

Using the following:

SQL Server: SQL Server 2012
Visual Studio 2012

I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.

General Tab:

Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
SQL Statement: EXEC FL_CUSTOM_sp_ml_location_load ?, ?;

Parameter Mapping:

Variable Name Direction Data Type Prmtr Name Prmtr Size
User: system_cd Input NVARCHAR 0 10
User: location_type_cd Input NVARCHAR 1 10

Variables:

location_type_cd - Data type - string; Value - Store (this is static)
system_cd - Data type - string - ??????
The system code changes based on the system field for each record in the load table

Sample Data:

SysStr # Str_Nm
3 7421Store1
3 7454Store2
1815061Store3
1815063Store4
1615064Store5
1615065Store6
1615066Store7
7725155Store8

STORED PROCEDURE: The stored procedure takes data from a load table and inserts it into another table:

Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................

This is an example of what I want to accomplish: I need to be able to group all system 3 records, then pass 3 as the parameter for system_cd, run the stored procedure for those records, then group all system 18 records, then pass 18 as the parameter for system_cd, run the stored procedure for those records and keep doing this for each different system in the table until all records are processed.

I am not sure how or if it can be done to pass the system parameter to the stored procedure based on the system # in the sys field of the data.

View 6 Replies View Related

SQL Reporting Services Issue Using Multiple Parameters Where Data Is Passed In From A Stored Procedure

Apr 15, 2008



I have an issue with using multiple parameters in SQL Reporting services where data is passed in from a stored procedure



When running the report in design mode - I can type in a parameter sting and it runs fine



In the report preview screen I can select single parameters by ticking the drop down list and again it runs fine



as soon as I tick more than one I get an error



An error occurred during local report processing

Query execution failed for data set €˜data'

Must declare the scalar variable '@parameter'



Some info...



The dataset 'workshop' is using a sproc to return the data string?

I get multiple values back fine in the sproc using this piece of code

(select [str] from iter_charlist_to_table( @Parameter, DEFAULT) ))



I have report parameters set to Multi-Value

Looking through the online books it says...



You can define a multivalued parameter for any report parameter that you create.

However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:

The data source must be SQL Server, Oracle, or Analysis Services.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
The query must use an IN clause to specify the parameter.

Am I trying to do the impossible ?

View 1 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

SQL Server 2012 :: Have Conditional Join / Union Based On Parameters Passed To Stored Procedure

Jul 15, 2014

I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.

I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.

View 6 Replies View Related

MSSQL 7 Replication - Creating Of Standard Sp_MS... Stored Procedures

Aug 15, 2001

I am running MS SQL Server 7 with SP3 installed and am having some problems getting replication to work correctly. I want to replicate all tables from one database to another database on the same server. I can setup replication, add publications and add subscriptions without any problem and all goes well until the replication process starts when the job aborts because it cannot find the sp_MSins..., sp_MSupd... or the sp_MSdel... stored procedures. I have used the New Publication wizard to create a Transactional publication, published all tables and allowed the wizard to use the default stored procedures which it says will be created when the subscribers are initialized but although I have created push subcriptions the stored procedures are never created and hence the replication fails whenever the job runs.

I have SA access to the databases concerned and as far as I am aware the jobs are running as my login - can anybody help me out as this is beginning to drive me nuts.

Thanks

Chris

View 1 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

MSSQL Stored Procedure

Apr 28, 2008

Hi.
Now I am learning mssql stored procedure and I faced on some trouble.
I want to make some stored procedure following as...
CREATE PROCEDURE PeopleSearch
(
@name nvarchar(50),
@country nvarchar(50),
@city nvarchar(5),
@condition integer
 ) 
My question from here...But I don't know how to build the code with IF conditions.
IF @condition = 0 THEN
"SELECT * FROM tbl_User WHERE fname='@name'"
IF @condition = 1 THEN
....SELECT Statement..
IF @condition = 2 THEN
....SELECT Statement..
I wish to make some condition in stored procedure like above style.
@condition is not field and it come from outside(vb behind-code).
Is it possible ? Please let me know....how do i have to do...

View 5 Replies View Related

MsSQL Stored Procedure Help

Jun 22, 2008

please help for MsSQL stored procedure for ASP.net/C#.  Inside stored procedure i used 4 different statements in 'where' clause .   first one is working but last 3's are not . Statements are given below-FILENAME LIKE ''%'+ @FILENAMETEXT +'%''             -----workingFILENAME LIKE '+ @FILENAMETEXT +'%''                 -----not workingSUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+'    ----not workingLEFT(FILENAME,4)='+@FILENAMETEXT+'                 -----not working bunch of code for 'where' clause are given below- DECLARE @FILENAMETEXT CHAR(4) DECLARE @CONDITION VARCHAR(2000) SET @FILENAMETEXT='http' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND FILENAME LIKE ''%'+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND FILENAME LIKE '+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND SUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' SET @CONDITION = ' WHERE (' SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'') AND LEFT(FILENAME,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 ' please reply me as soon as possible

View 1 Replies View Related

Creating Index In MSSQL 2000 From MSSQL 2005

Mar 24, 2008

Hi,

I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.

Ex:-

CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY

(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)

INCLUDE

(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);


Any help in creating such indexes in 2000 version is welcome.

Thanks,
Suresh.

View 2 Replies View Related

MSSQL Stored Procedure Query Using SELECT TOP

Dec 14, 2006

Is there a way to do a SELECT TOP # using a variable for the #?
In other words I'm doing a SELECT TOP 50* FROM DATATABLE
If I pass an @value for the number
SELECT TOP @value* FROM DATATABLE doesn't work
I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have.
Thanks in advance.

View 2 Replies View Related

Checking Constraint In A Stored Procedure + MSSQL 7.0

May 21, 2008

Hi i have a stored procedure which deletes a row from a table.
 Sometimes the proc will break the constraints for the table.
 How can i check if the constraint is going to be broken before I try to delete so that I can avoid a nasty error message coming onto my asp.net page?
 
Thanks in advance.
Gary

View 2 Replies View Related

Mssql 2005. How To Make Update Stored Procedure ?

May 1, 2008

Hi ~
I made simple stored procedure that is to update user information following as...
ALTER PROCEDURE UpdateUserProfile(  @user_id uniqueidentifier,  @user_firstname nvarchar(50),  @user_lastname nvarchar(50),  @user_birth nvarchar(20),   @user_gender nvarchar(20)  )
AS 
 UPDATE user_profile    SET         user_firstname = @user_firstname,     user_lastname = @user_lastname,     user_birth = @user_birth,     user_gender =  @user_gender  WHERE user_id = @user_id  RETURN
When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message.
What's the problem ? 
 

View 2 Replies View Related

Need Help In Writing A Stored Procedure In MSSQL Server 2000

Mar 29, 2007

Hi Everybody,

I am trying to update a column Percentage in a table named Critical Doctors with a column named

PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from

tblPercent.

I am getting an error message for the following query.

Have Two tables

1.CriticalDoctors
2.tblPercent

update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=

, >, >= or when the subquery is used as an expression.
The statement has been terminated.

Pls give me reply on how to write a stored procedure so that I can equate the percentage column

with one value and also check the condition with one value.

Thanking you guys in advance.

madhav

View 4 Replies View Related

Working In A Stored Procedure's Result Set In MSSQL 2000 (eg.: Selecting From)

Mar 4, 2004

Hy all.
My main goal would be to create some kind of map of the database, wich contains information of all connecting fields, including wich fields has key references to the other one and wich table. I'd like to have a table wich shows all the database connections tablename, field, field/table_key (from or to the key points), field_key_type (prymary or foreign)
So I thaugh to get sp_fkeys and sp_pkeys from master table and inner joining their results, but I simplay cannot "catch" their result sets. The script must have been written in SQL.
Obviously I'd like to do this:
SELECT * FROM EXEC sp_fkeys @table_name = 'xy'
of course it is not working this way, but I'd like something like this.


Please help! Thanx!

View 3 Replies View Related

Creating A Stored Procedure

Oct 13, 2006

Is it possible to create a stored procedure that execute a delete command from a table whose name is specified as a parameter of the stored procedure?Thank you 

View 3 Replies View Related

Help For Creating Stored Procedure

Apr 3, 2007

ALTER PROCEDURE companyinsert@companyid INT IDENTITY(1,1) NOT NULL,@companyname varchar(20),@address1 varchar(30)ASINSERT INTO companymaster                      ( companyname, address1)VALUES     (@companyname,@address1)i don't want the companyname having the same names are recorded again with the different company id..Can anyone help me and modify my code according it's giving error...in the @companyid.It is being done in sql server 2005 with asp.net C# 2005 

View 1 Replies View Related

Help Creating A Stored Procedure

May 6, 2007

I have some script for creating the table i want but i need some help creating a Stored Procedure. That will Insert the UserId from the aspnet_Users Table (UserId) into the UserId Column in my table. So when a user creates an account it will put that users id into my table. The data will be retrieved by a FormView and the user can Update their info threw the FormView control..
I just need to associate the UserId from the aspnet_User Table with my table, so the data is stored per UserId in a new row for each new user..create table dbo.custom_Profile (
UserId uniqueidentifier not null Primary Key,
IamWeAre nvarchar(50) null,
InterestedIn nvarchar(256) null,
IntroTitle nvarchar(100) null,
TellOthers nvarchar(MAX)null,
MaritalStatus nvarchar(20) null,
BodyType nvarchar(50) null,
Race nvarchar(20) null,
Smoking nvarchar(20) null,
Drinking nvarchar(20) null,
Drugs nvarchar(20) null,
Education nvarchar(256) null)

go 

View 15 Replies View Related

Creating Stored Procedure

Mar 16, 2008

I have a table (displayed in a gridview) of services we provide. I have another table (the logfile) that displays the current status of those services. This GridView displays the service and current status.When a new service is created there is obviously no status yet. This causes a problem because my stored procedure (below) does not display that new service in my GridView. How can I ensure EVERY service is included in my dataset regardless of whether or there is a status?  (and how can I get away from having to group by all the time?)
select s.servicename, s.opr, c.commentid,c.comment, c.etic, t.statusfrom svc_service sinner join svc_comment c on c.serviceid = s.serviceidinner join svc_status t on t.statusid = c.statusidgroup by s.servicename, s.opr, c.commentid, c.comment, c.etic, t.status
 TIA,
Jon

View 4 Replies View Related

Creating Stored Procedure

Jul 8, 2004

I have an inline sql query which i want to convert to a stored procedure.
The query is simplified as follows:

string sql = "SELECT * FROM property_property WHERE Location = " + location;
if(state != null) sql += " AND State = " + state;
if(cost !=null) sql += " AND Cost = " + cost;

The query is created depending on variable states; there are 7 in the real one which together creates the required query to query the database. Im not sure how/if it can be converted in to a stored proc

I know some of the basics and managed to convert every other query except this.

Any help would be appreciated

View 3 Replies View Related

Needs Help With Creating A New Stored Procedure

Dec 4, 2005

I already know how you create a stored procedure to add information to a database or retrieve a value for one record. But I don't know how to create a stored procedure that will retrieve many records for a certain querystring value.
Here's my simple stored procedure to show one record:
CREATE PROCEDURE DisplayCity(@CityID int)AS
SELECT City From City where CityID = @CityIDGO
My code for displaying the City:
Sub ShowCity()
    Dim strConnect As String
    Dim objConnect As SqlConnection
    Dim objCommand As New SqlCommand
    Dim strCityID As String
    Dim City As String
  
 
    'Get connection string from Web.Config
    strConnect = ConfigurationSettings.AppSettings("ConnectionString")
    objConnect = New SqlConnection(strConnect)
 
    objConnect.Open()
 
    'Get incoming City ID
    strCityID = request.params("CityID")  
 
   
    objCommand.Connection = objConnect
    objCommand.CommandType = CommandType.StoredProcedure
    objCommand.CommandText = "DisplayCity"
 
    objCommand.Parameters.Add("@CityID", CInt(strCityID))
   
   
    'Display SubCategory
    City = "" & objcommand.ExecuteScalar().ToString()
    lblCity.Text = City
     
      lblChosenCity.Text = City
  
    objConnect.Close()
 
End Sub

Here's the code I'd like to get help with changing into a stored procedure:
Sub BindDataList()
 
             Dim strConnect As String
             Dim objConnect As New System.Data.SqlClient.SQLConnection
             Dim objCommand As New System.Data.SqlClient.SQLCommand
             Dim strSQL As String
             Dim dtaAdvertiser As New System.Data.SqlClient.SQLDataAdapter()
             Dim dtsAdvertiser As New DataSet()
             Dim strCatID As String
             Dim strCityID As String
             Dim SubCategory As String
             Dim SubCategoryID As String
             Dim BusinessName As String
             Dim City As String
 
            
             'Get connection string from Web.Config
             strConnect = ConfigurationSettings.AppSettings("ConnectionString")
 
             objConnect = New System.Data.SqlClient.SQLConnection(strConnect)
    
             objConnect.Open()
                   
                   'Get incoming querystring values
              strCatID = request.params("CatID")
              strCityID = request.params("CityID")
             
             
 
             'Start SQL statement
             strSQL = "select * from Advertiser,AdvertiserSubCategory, Categories, SubCategories, County, City"
             strSQL = strSQL & " where Advertiser.CategoryID=Categories.CategoryID"
             strSQL = strSQL & " and Advertiser.AdvertiserID=AdvertiserSubCategory.AdvertiserID"
             strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID=SubCategories.SubCategoryID"
             strSQL = strSQL & " and Advertiser.CountyID=County.CountyID"
             strSQL = strSQL & " and Advertiser.CityID=City.CityID"
             strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID = '" & strCatID & "'"
             strSQL = strSQL & " and Advertiser.CityID = '" & strCityID & "'"
             strSQL = strSQL & " and Approve=1"
           strSQL = strSQL & " Order By ListingType, BusinessName,City"
 
  
 
 
        'Set the Command Object properties
        objCommand.Connection = objConnect
        objCommand.CommandType = CommandType.Text
        objCommand.CommandText = strSQL
 
        'Create a new DataAdapter object
        dtaAdvertiser.SelectCommand = objCommand
 
        'Get the data from the database and
        'put it into a DataTable object named dttAdvertiser in the DataSet object
        dtaAdvertiser.Fill(dtsAdvertiser, "dttAdvertiser")
       
        'If no records were found in the category,
        'display that message and don't bind the DataGrid
                 if dtsAdvertiser.Tables("dttAdvertiser").Rows.Count = 0 then
 
                         lblNoItemsFound.Visible = True
                         lblNoItemsFound.Text = "Sorry, no listings were found!"
                                                                   
                 else
 
        'Set the DataSource property of the DataGrid
        dtlAdvertiser.DataSource = dtsAdvertiser 
 
      'Set module level variable for page title display
        BusinessName = dtsAdvertiser.Tables(0).Rows(0).Item("BusinessName")
        SubCategory = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategory")
        SubCategoryID = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategoryID")
        City = dtsAdvertiser.Tables(0).Rows(0).Item("City")
  
        'Bind all the controls on the page
        dtlAdvertiser.DataBind()    
 
             
      
        end if
       
        objCommand.ExecuteNonQuery()
       
        'this is the way to close commands
        objCommand.Connection.Close()
       
        objConnect.Close()
 
End Sub   


 
 

View 4 Replies View Related







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