When I created an SP in Enterprise Manager, I didn't manually type in the existence check at the inception, a la "if exists (select * from sysobjects...)". I just started with the CREATE PROC AS statement.
I noticed that if I generate a SQL script for the SP, SQL2000 automatically generates the existence check statement.
My question is, can I assume that when the SP is actually executed, SQL2000 does the exists check on its own? I EXEC'd the proc in Query Analyzer with no errors.
I just want to make sure that I don't need to enter the exists statement if it's already being done behind the scenes.
I'm trying to create a batch sql script which first alters some existing tables via the ALTER TABLE command, I then want to alter some existing stored procedures via the ALTER PROCEDURE command within the same batch. I have found that I can encompas the alter table scripts within a conditional IF EXISTS (Begin/End) but not the alter procesdure scripts. I have looked in reference material and have found nothing to suggest this type of operation is not possible. Is this possible? Is this a know bug fixed by a service pack?
Louis writes "Is it possibble to write a SQL statement that returns "True" or "False" if a stored procedure exists. Basically write a SQL statement that checks whether or not the Stored Procedure exist in the database???
Hi,In my stored procedure I would like to do something like :IF EXISTS(SELECT WebIdFROM WebsWHERE FormatDateId = @FormatDateId)BEGINSELECT WebIdFROM WebsWHERE FormatDateId = @FormatDateIdSET @RetVal = -1ENDIs there a nicer way to do this, so that we don't have to do therequest twice ?Regards
I'm trying to put scripts to create our stored procedures under version control.
However I don't want these scripts to be run if the stored procedure already exists (we'll be using update scripts to alter existing stored procedure I think).
Anyway I tried :
Code:
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
/****** Object: Stored Procedure dbo.getEnglandHotelsByATOPResort Script Date: 26/10/2005 10:40:01 ******/ if NOT EXISTS (SELECT object_id('procedureName','p')) CREATE PROCEDURE [dbo].[procedureName] @location char(2) AS ...Procedure...
GO
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I know that the if NOT EXISTS ... part works as doing the following returns the expected result :
Code:
IF NOT EXISTS (SELECT object_id('procedureName','p')) SELECT 'FALSE' ELSE SELECT 'TRUE'
However when trying to do this before the CREATE PROCEDURE I get :
Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'PROCEDURE'. Server: Msg 137, Level 15, State 1, Line 12 Must declare the variable '@location'.
Hey,I'm writing some automated unit tests for a database app. The appcreates a database via script. I'm developing the system in atest-driven manner and need a way to determine if a parameter existswithin a stored procedure using SQL. Any ideas?Thanks,WillJoin Bytes!
I'm writing some code to create stored procedures in a database. In order to test it out, I deleted a stored procedure (right clicking in SQL Server 2008 and clicking on delete) and then ran my code to see if it would create it.
My code looks like this:
Code: SqlCommand command = new SqlCommand(); SqlConnection conn = database.TypeLibraryDatabaseConnection; command.Connection = conn; // create the command to create the stored procedure command.CommandText = database.GetProcedure(node.Name); // create the stored proc in the database try { command.ExecuteNonQuery(); } catch { } command.Dispose();
database.GetProcedure(node.name) basically gets a string containing the SQL script to create the stored procedure.
command.ExecuteNonQuery() throws an SqlException that says: "There is already an object named 'SecuritySession_DeleteSessionById' in the
database." But I deleted it! Why does it think it's still there?
I Have a nifty little stored procedure that takes data from an ASP form post and inserts the data into two tables. However what I really need this to do before the insert is check whether there is already a record that matches some of the criteria, and if so returns an error "This Username already exists" and only if there isn't a record that matches the criteria is the record inserted.
my current stored procedure looks like this -
Code Snippet@siteid int,@companyname nvarchar(50),@address nvarchar(500),@phone nvarchar(50),@fax nvarchar(50),@email nvarchar(225),@url nvarchar(225),@companytype nvarchar(50),@billingcontact nvarchar(50),@name nvarchar(50),@AccountType nvarchar(50),@PASSWORD nvarchar(50),@AccountLive nvarchar(50),@EmployeeLevel nvarchar(50)ASDeclare @NewID INTINSERT INTO dbo.JBClient(JBCLSiteID, JBCLName, JBCLAddress, JBCLPhone, JBCLFax, JBCLEmail, JBCLCompanyType, JBCLURL, JBCLAccountType, JBCLAccountlive, JBCLBillingContact)VALUES (@siteid, @companyname, @address, @phone, @fax, @email, @companytype, @url, @AccountType, @AccountLive, @billingcontact)SELECT @NewID = SCOPE_IDENTITY()INSERT INTO dbo.JBEmployee(JBEClientID, JBESiteID, JBEName, JBELevel, JBEUsername, JBEPassword, JBEAddress, JBEPhone)VALUES (@NewID, @siteid, @name, @EmployeeLevel, @email, @PASSWORD, @address, @phone) The values that i need to check against are -
@siteid @email
in the table dbo.JBEmployee against columns JBESiteID & JBEUsername
What i would really like to do if a record exists is return the user to an ASP page, which contains all of the variables previously enterred -,
How do you use an if then statement in a sql stored procedure? I have a table with 3 columns in it. When the tableis updated, I will only need one particular column in the database updated at a time. I will signal which column to update with an integer value(WhichColumn). So I want to key off that value to determine which column is to be updated. Here is my pseudo code if(WhichColumn EQUALS 1){ Update InsertEntry SET FirstColumn=Value}else if(WhichColumn EQUALS 2){ Update InsertEntry SET SecondColumn=Value}else if(WhichColumn EQUALS 3){ Update InsertEntry SET ThirdColumn=Value} Here is a rough start to my stored procedure to incorporate the logic aboveGOCREATE PROCEDURE [dbo].[InsertEntry]( WhichColumn int Value nvarchar(500), )
Hey all, don't know where I am going wrong on this, and maybe it's just that it can't be done. Below is the stored procedure and page code, and after that is the description of the prob************************* SP_GetDetails @wherestring varchar(400) ASSelect title_id, title, type, ytd_sales, pub_id from titles where title_id IN (@wherestring)*****************************This is the function that calls SP_GetDetails*****************************Public Function GetDetails(ByVal title As String) As DataViewDim DS As DataSetDim myconnection As SqlConnectionDim mycommand As SqlDataAdaptermyconnection = New SqlConnection(_ConnString)mycommand = New SqlDataAdapter("SP_GetDetails", myconnection)mycommand.SelectCommand.CommandType = CommandType.StoredProceduremycommand.SelectCommand.Parameters.Add(New SqlParameter("@wherestring", SqlDbType.VarChar, 400))mycommand.SelectCommand.Parameters("@wherestring").Value = titleDS = New DataSetTry mycommand.Fill(DS, "Details") Return DS.Tables("Details").DefaultViewCatch ex As Exception Throw exFinally myconnection.Close()End TryEnd Function****************************And this is the Function that Calls GetDetails:****************************Public Sub Get_Info(ByVal Sender As Object, ByVal e As EventArgs)Dim data As New DataLayerDim titles As String = ""Dim item As ObjectDim ltitle As StringDim i As Integer = 0For Each item In ListBox2.Items() If ListBox2.Items(i).Selected Then ltitle = ListBox2.Items(i).Value.ToString If (i > 0) And Not (titles = "") Then titles += ", '" & ltitle & "'" Else titles += "'" & ltitle & "'" End If End If ltitle = "" i += 1NextDataList1.DataSource = data.GetDetails(titles)DataList1.DataBind()End Sub****************************Problem:The DataSet is not getting any values. If I run the procedure in the QA with values that I know work, in the place of @wherestring, it works fine and I get the proper results. However if I try and give it a value (ie: Get_Info() returns "'BU1032', 'BU1111'", which are valid values, it doesn't respond) I have a feeling that its the way that the information @wherestring gets isn't formated right to be able to plug into the SP but I don't know any other way to do it...help is greatly appreciated and if you have any questions I will answer back immediately. thanks a ton. --Shred
I have a stored procedure that returns employee id's and how many shifts they have signed up for between 2 dates.
If they have less then 3 entries between the date range specified I would like update their status field to inactive.
So what I'm getting at is how would I go about doing an IF statement that would check through the results of my stored procedure to see who has worked less then 3 shifts and to execute another stored procedure to update their status.
I am creating am trying to create a stored procedure that looks to see what group_id number a user selected and if they select no group_id number than it should return one of a specific set that I specify in my procedure, but if they pick just one group_id number I only want it to return those records. Here is my procedure. When I run in Managment Studio it says I have errors near my if, then and else.
Any Ideas?? Thanks in advance!
CREATE PROCEDURE EMRUserSecurityGroups @group_id int
If @group_id = "*All" then SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name FROM user_group_xref a JOIN security_groups b on a.group_id= b.group_id JOIN user_mstr c on a.user_id=c.user_id WHERE b.group_id IN ('5','20','21','23','24','25','26','27','28','29','32') else SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name FROM user_group_xref a JOIN security_groups b on a.group_id= b.group_id JOIN user_mstr c on a.user_id=c.user_id WHERE b.group_id = @group_id
Hi Firends - What is good to use if we have to retrive records from SQL database. Should I use Stored procedures or should I write SQL statemnt in my Code.
Please let me know the advantages and disadvantages of both
Here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@pswd varchar(20),@no_go int OUTPUTASSET NOCOUNT ONSELECT user_id FROM tableWHERE user_id=@id AND pswd=@pswdIF @@ROWCOUNT = 0BEGINSET @no_go = 1ENDELSEBEGINSELECT date,date_mod FROM ansWHERE user_id=@idSET @no_go = 0ENDThis statement outputs the second recordset (SELECT FROM ans) whether@@ROWCOUNT is 0 or not. Why is that and how do I stop it
I have this code in a stored procedure: DECLARE @SQLString VarChar(200) SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')' Exec (@SQLString) it works fine - with just one issue - I must grant select permission on the table. Is there a way to do this WITHOUT granting the select permissions?
Is it necessary to include the Begin and End in this statement? IF @CId = '102' BEGIN SET @102 = 1 ENDOr, can it be rewritten as... IF @CId = '102' SET @102 = 1 Thanks all,Zath
Hello, I need a little help turning this:SELECT RequestNum FROM Tickets WHERE ReceiptDate>='" & FromDate & "' AND ReceiptDate<='" & ToDate & "'"into a sproc because of the two different values (FromDate and ToDate) for the ReceiptDate field in the database.I have this so far (problem areas are ??):Dim AuditConnection As New SqlConnection(ConnString)Dim AuditCommand As New SqlCommand("CreateAudit", AuditConnection)AuditCommand.CommandType = CommandType.StoredProcedureAuditCommand.Parameters.Add(New SqlParameter("@??", SqlDbType.NVarChar)).Value = FromDateAuditCommand.Parameters.Add(New SqlParameter("@??", SqlDbType.NVarChar)).Value = ToDateAuditConnection.Open()Dim AuditResult As SqlDataReader = AuditCommand.ExecuteReader()AuditGrid.DataSource = AuditResultAuditGrid.DataBind()AuditConnection.Close()and:CREATE PROCEDURE CreateAudit ?? ??ASSELECT RequestNumFROM TicketsWHERE ??AND ??GOI know I'm an idiot and this should be something simple. Arrrgh. Any help is appreciated immensely!!! :)
I'm trying to handle a stored procedure parameter. What needs to happen is that a particular statement shouldn't be executed if the parameter is empty. However, when I try it I get the following error:Cannot use empty object or column names. Use a single space if necessary.So, what's the correct way of doing the following?IF @filename <> ""BEGIN UPDATE Experimental_Images SET contentType = @contentType, filename = @filename WHERE (id = @iconNo)END
I want to run a stored procedure which updates a password. I have the a table 'users' which has columns 'name' (as primary key) and 'pwd', which hold the username and password respectively. The stored procedure accepts @username (the username), @pwdOld and @pwdNew (the old and new passwords).
The following procedure returns 1 if the user inputs the correct old password.
The following procedure updates the old password to the new password. So job done.
strCreateStoredProcedure = " " & _
"CREATE PROC changePassword2 " & _ "( " & _
"@userName VarChar(20), " & _
"@pwdOld VarChar(50), " & _
"@pwdNew VarChar(50) " & _ ") " & _ "AS " & _
"Declare @name VarChar(20) " & _
"Declare @actualPassword VarChar(50) " & _
" UPDATE users " & _
" SET pwd = @pwdNew " & _
" WHERE name = @userName "
But using two procedures must be inefficient and slow. However, I have not been able to combine the two. I would have thought I should be able to replace "return 1" in the first procedure with the UPDATE statement in the second procedure but I cannot save this procedure. i.e.
SELECT * FROM Cards WHERE CASE @Type = 1111 THEN CardType = 1111 ELSE CardType = 2222 END
GO
I know that the part after WHERE is wrong. But what I would like to achieve is this:
if the @type variable equals 1111 then get alla the rows with that value in the CardType-column. The same if @type = 2222, and if @type is any other value, then choose all rows regardles of the CardType value.
hi all, i'm wondering if i can use one stored procedure in too cases, this is the senario: i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user if there a way to use if and else to make this happen this is what i did so far:
CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as if @indexType = 1
begin
SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin) end else begin SELECT * FROM table WHERE user = @user end GO
FoodID Integer PRIMARY KEY, FoodName varchar(255), FoodDesc text
How do I insert into this table while checking that the FoodName do not replicate? I'm aware that with a stored procedure I'm able to you the IF EXIST statement to help me solve this problem. But if I do not wish to use the stored procedure, am I able to create a SQL string to insert while checking the condition?
Thanks in advance to the people that replied to my request! Thanks so much...
How to compare the Test_Result Column With Normal Values and Update the Test_Status Column for multiple rows in Transaction Table.
If my Test_Result value is less than Low Value of Normal_Values then i need to Update Test_Status as L and if Test_Result value is greater than High Value of Normal_Values then i need to Update Test_Status as H and if the Test_Result Value is in between Low and High Value of Normal_Values then i need to update a blank space
SELECT ActivityCode AS 'Activity Code', ActivityDate AS 'Activity Date', ActDescription AS 'Activity Desc', ActDetail AS 'Activity Detail', ActSource AS 'Activity Source', ActPrjSource AS 'Activity Type', ActBillable AS 'Billable', ActBillRate AS 'Bill Rate', ActBilltoCCName AS 'Bill to Cost Center', (CASE WHEN ActARClosed = 1 Then 'Billed' Else 'Not Billed' END) AS 'Billed Status', ActBillingType AS 'Billing Type', ActBudType AS 'Budget Type', ActBudCat AS 'Budget Cat', ActBudSubCat AS 'Budget Sub Cat', ActCompMonth AS 'Company Month', ActCompWeek AS 'Company Week', ActCompYear AS 'Company Year', ActCostRate AS 'Cost Rate', ActCustName AS 'Customer', ActCustMngr AS 'Customer Manager', ActCustomerPct AS 'Customer Percentage', ActCustPO AS 'Customer PO', ActDataSplash AS 'Data Splash', ActDocId AS 'Document ID', ActEmpName AS 'Employee', ActEmpCCCode AS 'Employee CC Code', ActEmpCCGroup AS 'Employee CC Group', ActEmpCCName AS 'Employee Cost Center', ActEmpCCGrp3 AS 'Employee Department', ActEmpCCGrp2 AS 'Employee Division', ActEmpFTE AS 'Employee FTE', ActEmpMgr AS 'Employee Manager', ActEmpPos AS 'Employee Position', ActEMpTaskName AS 'Employee Task', (CASE WHEN ActFromType = 'Expense Report' THEN ActMoneyValue WHEN ActFromType = 'Purchase Request' THEN ActMoneyValue ELSE 0 END) AS 'Expenses' , ActFromType AS 'Form Type', ActGroups AS 'Groups', (CASE WHEN ActARClosed = 1 THEN ActBillTotal ELSE 0 END) AS 'Income', ActInvoiceNum AS 'Invoice Number', (CASE WHEN ActAPClosed = 1 Then 'Paid' Else 'Not Paid' END) AS 'Paid Status', ActPrjCCName AS 'Project Cost Center', ActPrjCCCode AS 'Project CC Code', ActPrjCode AS 'Project Code', ActPrjCCGrp3 AS 'Project Department', ActPrjSource as 'Project Source', ActPrjIndustry AS 'Project System', ActPrjMgr AS 'Project Manager', ActPrjName AS 'Project Name', ActPrjPhase AS 'Project Phase', ActPrjSponsor AS 'Project Sponsor', ActPrjType AS 'Project Type', ActTaskPhase AS 'Task Phase', (CASE WHEN ActFromType = 'Time Card' THEN ActTimeValue ELSE 0 END) AS 'Time', ActWeekEndDate AS 'Week Ending Date', ActPersFlowTxt1 AS 'Person Custom Text 1', ActPersFlowTxt2 AS 'Person Custom Text 2', ActPersFlowTxt3 AS 'Person Custom Text 3', ActProjFlowTxt1 AS 'Project Custom Text 1', ActProjFlowTxt2 AS 'Project Custom Text 2', ActProjFlowTxt3 AS 'Project Custom Text 3', ActHeaderCustom1 as 'Header Custom Text 1', ActHeaderCustom2 as 'Header Custom Text 2', ActHeaderCustom3 as 'Header Custom Text 3', ActHeaderCustom4 as 'Header Custom Text 4', ActDetailCustom1 as 'Detail Custom Text 1', ActDetailCustom2 as 'Detail Custom Text 2', ActDetailCustom3 as 'Detail Custom Text 3', ActDetailCustom4 as 'Detail Custom Text 4'
FROM ACTIVITIES WHERE ActivityDate BETWEEN @StartDate AND @EndDate AND (ISNULL(ActPrjName,'') LIKE @PrjName ) AND (ISNULL(ActEMpTaskName,'') LIKE @TaskName ) AND (ISNULL(ActEmpName,'') LIKE @EmpName )
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
*************
The goal is to add another table called EMPLOYEES with fields E1 and E2 as part of the returned data set. I need a join statement but I can't figure out the syntax that will actually pull the data. Any help would be appreciated. Thanks.
I am using C# to pass few paramenters to build the SQL statement in the stored procedure but when i run it i get this error:
Could not find stored procedure 'SELECT file_no, old_file_no, id_number, person_name FROM persons WHERE gender IN (null, 'MALE', 'FEMALE', 'OTHER') ORDER BY NAME'.
though the stored procedure dose exist and i don't have any problem when i staticaly write the sql statement in the stored procedure. can you pleas help..
here is my store dprocedure:
Code Block USE shefa set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= ALTER PROCEDURE [dbo].[sp_get_all_patients] @order_field varchar(255), @patient_gender varchar(255) AS BEGIN SET NOCOUNT ON; DECLARE @sql_statement varchar(255)
SET @sql_statement = 'SELECT file_no, old_file_no, id_number, person_name FROM persons ' + @patient_gender + ' ORDER BY ' + @order_field EXEC @sql_statement -- IF @order_field = 'FILE NO' -- SELECT file_no, old_file_no, id_number, person_name FROM persons ORDER BY file_no -- ELSE IF @order_field = 'OLD FILE NO' -- SELECT file_no, old_file_no, id_number, person_name FROM persons ORDER BY old_file_no -- ELSE -- SELECT file_no, old_file_no, id_number, person_name FROM persons ORDER BY person_name END
and here is my C#:
Code Block private void get_all_patients(string order_field, string patient_gender) { this.Cursor = Cursors.WaitCursor; data_table = new DataTable(); // sql_connection = new SqlConnection("Data Source=.\SQLEXPRESS;initial catalog=shefa;integrated security=true"); sql_connection = new SqlConnection(public_var.sql_connection); sql_command = new SqlCommand("sp_get_all_patients", sql_connection); sql_command.CommandType = CommandType.StoredProcedure; sql_command.Parameters.Add("@order_field", SqlDbType.VarChar).Value = order_field;
if (patient_gender == "ALL") sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender IN (null, 'MALE', 'FEMALE', 'OTHER'"; else if (patient_gender == "MALE ONLY") sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender ='MALE'"; else if (patient_gender == "FEMALE ONLY") sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender ='FEMALE'"; else if (patient_gender == "OTHER") sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender NOT IN ('MALE', 'FEMALE')"; else sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender IN (null, 'MALE', 'FEMALE', 'OTHER')"; data_adapter = new SqlDataAdapter(sql_command); data_adapter.Fill(data_table); dataPatients.DataSource = data_table;
I have written a stored procedure which has in it a select statement. This returns a single record (I used the "top 1" line to ensure I only get a single record). I want to use a value returned by this statement (not the primary key) further on in the stored procedure - so assigning it to a variable would be handy.