I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
Hi everyone: I need to use the "SET ROWCOUNT" statement to limit the amount of data returned to the application in a query, I know that if "SET ROWCOUNT = 0" is not specified at the end of this stored proc all the next queries will return only the amount of records specified in the initial "SET ROWCOUNT" call, so I would like to know if a I can have something like the TRY-CATCH-FINALLY statement (in SQL-92 for SQL Server 2000, not in SQL 2005) to make sure the "SET ROWCOUNT = 0" is sent at the end even if an error is raised. Can it be done? Thanks for any help.
Is there any way to prevent unimportant errors in a stored procedure from causing exceptions in my C# code? This is preventing the SqlAdapter from filling the query results into my DataSet.
The Setup:
I have a Stored Procedure in Sql Server 2000 which has a text parameter called @Xml. I send in an Xml document to process. This document contains several "records" to process. The format of the xml really isn't important.
I create a temporary table called #Results to hold the results of processing each record in the xml.
To process the xml I have a Cursor which loops over a SELECT from the xml.
For each record, the sproc attempts to make a series of INSERTs and UPDATEs inside of a transaction. Any one of these commands may fail because of constraint violations or attempts to insert NULL into non-null columns, or such. After each command I check @@ERROR. If it is not zero, I stop processing the record and rollback the transaction. The cursor loops around and tries the next record. Each time the success or failure of the transaction is recorded into the #Results table.
When the cursor is done looping I 'SELECT * FROM #Results'.
I've tested this many times in the Query Analyzer and each time, regardless of any errors, I can see the result set from the SELECT of the #Results table in the Grids tab. The Messages tab shows each of the errors that occurred.
I try to call this stored procedure using the following code:
The value of c will always be zero, if there were any errors during the execution of the stored procedure. The DataSet does not get filled, even though the stored procedure is returning a result set. This is a problem for me because I expect errors to occur, and I need to know which records from the Xml caused those errors.
Is there any way to clear the errors in my stored procedure so that they don't turn into exceptions in my code? Or, is there anyway to get the Adapter to fill the DataSet regardless of any errors that were encountered?
I've also tried this with a SqlDataReader. The reader never gets assigned to because SqlCommand.ExecuteReader() throws an exception.
Hi there,I am converting a large PL/SQL project into Transact-SQL and have hitan issue as follows:I have a PL/SQL procedure that converts a string to a date. Theprocedure does not know the format of the date in the string so ittries loads of formats in converting the string to a date until itsucceeds.After trying each potential format it uses the Oracle 'EXCEPTION WHENOTHERS' construct to trap the failure so it can try another format.Is it possible to do this with SQLServer ? If I do a CONVERT and it isnot one of the standard formats it fails. This is part of a backgroundscheduled process and I cannot afford the procedure to bomb out.I suspect the answer is I cannot do this and will need to impose somecontrol over the string being received (from various externalsystems!!) to ensure it is a specific known format. Even if I know itwill be one of the known SQLServer formats this will not be enoughsince if the first one I try is not correct the process will crash.Any ideas ?Thanks
create proc dbo.sp_GetInvitationsHistoryDetails(@ExecID int, @OrgID int) as IF (@OrgID = 0) BEGIN select E.EventID,E.Description as Event,E.EventDate as Date I.Attending as [Att'g],NotAttending as [Not att'g],I.Bootcamp as [Maybe] I.Attended FROM Invitations I INNER JOIN Events E on I.EventID=E.EventID WHERE I.MemberID=@ExecID and NotForStats=0 ORDER BY E.EventDate DESC END ELSE BEGIN select E.EventID,E.Description as Event,E.EventDate as Date count(*) as Invited,SUM(CONVERT(smallint,I.Attended)) AS Attended FROM Invitations I INNER JOIN Events E on I.EventID=E.EventID WHERE I.MemberID IN (select ID FROM Executives WHERE OrganisationID=OrgID GROUP BY E.EventID,E.Description,E.EventDate ORDER BY E.EventDate DESC END
and i'm getting the following syntax errors when i check it -
Msg 102, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 10
Incorrect syntax near 'I'.
Msg 102, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 19
Incorrect syntax near 'count'.
Msg 156, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 23
Incorrect syntax near the keyword 'ORDER'.
Originally i just ran this sql from C# and it worked, obviously added the @ to the variables but it's basically the same.
Hello,I have a CLR stored procedure which send some values to an external URL by using the webclient, but for some reason I am getting this error.A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_LeadSend": System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Net.WebClient.UploadValues can anyone please advice how to resolve this one...I am really having a hard luck....... thanks.
Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...
my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...
if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...
this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...
but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??
I have a stored procedure (works form the SQL side). It is supposed to update a table, however it is not working, please help. What is supposed to happen is I have a delete statement deleting a payment from the payment table. When the delete button is pushed a trigger deletes the payment from the payment table and transfers it to the PaymentDeleted table. The stored procedure is supposed to update the PaymentDeleted table with the empID and reason for deleting, the delete and transfer work fine, however these 2 fields are not updated. Below is the sp and below that is the vb code. Thanks, Karen
ALTER PROCEDURE dbo.PaymentDeletedInfoTrail (@EmpID_WhoDeleted varchar(10), @Reason_Deleted varchar(255), @PmtDeletedID int) AS BEGIN
UPDATE dbo.PaymentDeleted SET EmpID_WhoDeleted = @EmpID_WhoDeleted WHERE PmtDeletedID = @PmtDeletedID
UPDATE dbo.PaymentDeleted SET Reason_Deleted = @Reason_Deleted WHERE PmtDeletedID = @PmtDeletedID
END
Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click Me.Validate() If Me.IsValid Then Dim DR As SqlClient.SqlDataReader
Dim strPmtID As String strPmtID = lblPmtIDDel.Text
Dim MySQL As String MySQL = "DELETE From Payment WHERE PmtID = '" & strPmtID & "'" Dim MyCmd As New SqlClient.SqlCommand(MySQL, SqlConnection1) SqlConnection1.Open() DR = MyCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection) SqlConnection1.Close()
Dim strDeletePmt As String strDeletePmt = lblPmtIDDel.Text
Dim cmd As New SqlClient.SqlCommand("PaymentDeletedInfoTrail", SqlConnection1) cmd.CommandType = CommandType.StoredProcedure
Dim myParam As SqlClient.SqlParameter myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@PmtDeletedID", SqlDbType.Int)) myParam.Direction = ParameterDirection.Input myParam.Value = lblPmtIDDel.Text
I have a stored procedure that dynamically bulk loads several tables from several text files. If I encounter an error bulk loading a table in the stored procedure, all I get is the last error code produced, but if I run the actual bulk load commands through SQL Management Studio, it gives much more usable errors, which can include the column that failed to load. We have tables that exceed 150 columns (don't ask), and having this information cuts troubleshooting load errors from hours down to minutes. Onto my question..., is there any way to capture all of the errors produced by the bulk load from within a stored procedure (see examples below)?
Running this...
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
Produces this (notice column name at the end of the first error)...
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CustId).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Running this (similar to code in my stored procedure)...
BEGIN TRY
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Produces something similar to this (which is useless)... ...Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
I currently have a stored procedure that is defined as follows:
CREATE PROCEDURE UpdateSyncLog
@TableName char(100),
@LastSyncDateTime datetime,
@ErrorState int OUTPUT
I am using an execute sql task to call this procedure. The connectiontype is ADO .NET and the SQLSourceType is DirectInput. The IsQueryStoredProcedure setting is false, and the following is my SQL Statement I have entered:
Result set is set to None, as this query returns NO results (i.e. has no select statements in it that returns results).
I have two variables in this SSIS package. CurrentDateTime, and ErrorStateVal. CurrentDateTime is of Data type DateTime, the ErrorStateVal is of type Int32
The error I am getting when running this execute sql task is as follows:
Error: 0xC001F009 at AS400 to SQL Full Repopulation Sync: The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState" failed with the following error: "The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
This makes no sense to me, both the SSIS variable ErrorStateVal is Int32, as well as the parameter declaration in the Execute SQL task is Int32 with direction of OUTPUT, and my stored procedure definition has @ErrorState as an integer as well.
I have a web application that has a search engine that returns records based off what the user selects in the search engine. I am currently using coalesce in the where statement in my stored procedure to return the records. For eample, where field1= coalesce(@parm1,field1). I don't know if this example is better than building the sql statement dynamically in a parameter then executing the parameter with sp_executesql. Can someone explain to me which is better or if there is a better solution?
CREATE PROCEDURE ggg_test_sp @start_date datetime,@end_Date datetime AS
SET NOCOUNT ON DECLARE @sqlstmt varchar(1000)
SELECT @sqlstmt='SELECT * FROM ggg_emp WHERE date_join BETWEEN ' +CONVERT(varchar(10),@start_date-1,101) + ' AND ' +CONVERT(varchar(10),@end_Date+1,101)
SELECT @sqlstmt EXEC (@sqlstmt)
GO
I want to apply date filter in the above sp with dynamic sql stmt. When i execute the above procedure with date ranges( @start_date=07/06/2004 AND @end_Date= 08/06/2004)i am not getting any result because my @sqlstmt variable has the select stamet
SELECT * FROM ggg_emp WHERE date_join BETWEEN 07/06/2004 AND 08/06/2004
BUT it should have the sqlstmt as
SELECT * FROM ggg_emp WHERE date_join BETWEEN '07/06/2004' AND '08/06/2004' to produce the required result
I know that for the above SP we dont need any dynamic sql but this is just an example.
Can anyone help me with this dumb question? I want to use a stored procedure to bring back a recordset depending if a bit column is set to 1. My table has a number of columns that are of Data Type bit and I want to be able to specify which particular column I'm interested in as a parameter when I call the Stored Procedure.
I have set up the Stored Procedure as follows:
CREATE PROCEDURE getProducts @param1 varchar(50) AS SELECT ProductID, ProductName FROM dbo.Products WHERE @param1 = '1' GO
I have a stored procedure spGetAccessLogDynamic and when I try to callit I get the following error:Server: Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'S'.I dont know why because I dont have anything refering to storedprocedure 'S'I have ran my SQL String with sample values and it works fine. So Iam presuming that it is some kind of syntax error in my storedprocedure but have tried everything and cant find it!Anyway here is the sample data I am using to call it and my spExec spGetAccessLogDynamic '24', '2005/07/04 00:00:00 AM', '2005/11/0400:00:00 AM', 'TimeAccessed DESC'CREATE PROCEDURE spGetAccessLogDynamic(@PinServiceID varchar (4),@StartDate varchar(40),@EndDate varchar(40),@SortExp varchar (100))AS-- Create a variable @SQL StatementDECLARE @SQLStatement varchar-- Enter the Dynamic SQL statement into the variable @SQLStatementSELECT @SQLStatement = ( 'SELECT A.PinValue,A.TimeAccessed,C.Forename, C.SurnameFROM AccessLog A, Members C, Pins PWHERE P.PinValue = A.PinValue ANDP.MemberID = C.MemberID AND A.PinServiceID= ''' + @PinServiceID + '''AND A.TimeAccessed BETWEEN dbo.func_DateMidnightPrevious( ''' +@StartDate + ''' ) AND dbo.func_DateMidnightNext( ''' + @EndDate+''')GROUP BY A.PinValue,A.TimeAccessed, C.Forename, C.SurnameORDER BY ' + @SortExp)-- Execute the SQL statementEXEC ( @SQLStatement)GOAny help would be very very much appreciated!!!!!!ThanksCaro
We are continuing to have issues with a certain stored procedure using dynamic sql. The issue arose when we tried to clean the stored procedure up, and seemed to have zero problems in staging. As soon as we moved it into production, the stored proc caused excessive blocking and completely slowed down our production environment. We immediately rolled back the older version and production is back to normal.
After looking at the new procedure I don't understand how it could cause blocking. Any help is much appreciated!
Old Proc without issues---- -------- USE [Realist_Prod_1203] GO /****** Object: StoredProcedure [dbo].[USP_GetMatchedMLSRecord] Script Date: 12/04/2007 09:33:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /* ===================== Created By: Sunil/Sudeep 19-11-2003 Description: Does a lookup of MLS Property data for reverse link. This is susceptible to error in that if erroneous data is given to us,it will not find a match. For this reason, commented out the lookup on Suffix and changed the street to use a like clause. Many users are putting the suffix in the street clause and no hits are generated. This hurts performance, but it improves the hit ratio.
Mods: 01/08/2004 - Balawant - Added nullif(), as it was comparing apn numbers with '' (empty space) 02/23/2004 - Balawant - Added or (or State = '') condition for state, zip, city, StreetDirection and Suffix. 11/18/2004 - Sunil Padmanbhan - Added begin-end and modified altapn and parcelid in nullif statment. 04/03/2007 - Shiny - changed to Parameterized query generation 04/03/2007 - Vasan - Removed redundant nullif's and added a limit of 100 records on output 04/03/2007 - Shiny - Removed more Nullif's and changed datatypes for Zip and CountyID to Char to match with table datatypes 04/05/2007 - Vasan - Modified to match resultsets with original procedure ===================== if exists (select 1 from sysobjects where name = 'USP_GetMatchedMLSRecord') drop procedure USP_GetMatchedMLSRecord grant exec on USP_GetMatchedMLSRecord to webuser */ CREATE PROCEDURE [dbo].[USP_GetMatchedMLSRecord] ( @GroupID int, @HouseNumber varchar(50), @StreetDirection varchar(50), @StreetName varchar(50), @Suffix varchar(50), @Unit varchar(50), @City varchar(50), @State varchar(50), @ZIP char(50), @FIPS varchar(10), @ApnNumber varchar(50), @AltApn varchar(50), @ParcelId varchar(50), @ReverseLinkURL varchar(200) ) AS DECLARE @CountyID char(6) Select @CountyID=CountyID from ltCounties where FIPS=@FIPS IF (@ApnNumber IS NOT NULL AND @ApnNumber <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@ApnNumber AND GroupID=@GroupID )) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@ApnNumber AND GroupID = @GroupID ; ELSE BEGIN IF (@AltApn IS NOT NULL AND @AltApn <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@AltApn AND GroupID=@GroupID)) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber= @AltApn AND GroupID=@GroupID; ELSE IF (@ParcelId IS NOT NULL AND @ParcelId <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WHERE APNnumber=@ParcelId AND GroupID=@GroupID )) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber= @ParcelId AND GroupID=@GroupID; ELSE BEGIN -- Finalize parameter values IF @ReverseLinkURL IS NULL SET @ReverseLinkURL = ''; IF @StreetName IS NOT NULL AND @StreetName <> '' SET @StreetName = @StreetName + '%'; -- Build up SQL text dynamically, only including filter predicates for those parameters that the user wants -- to search on. DECLARE @sqltext nvarchar(4000) SET @sqltext = 'Select top 100 '''' + @ReverseLinkURL as ''ReverseLinkBaseURL'',MLSNumber,Comment from tblMLSListing WITH (NOLOCK) where ' -- Because of skew and relative few group IDs, you may want to use an inline literal for this one parameter -- to avoid plan sharing across different GroupIDs. Use explicit parameterization for the other parameters. if @GroupID is null set @sqltext = @sqltext + '1=1' --ignore Group_ID if null else SET @sqltext = @sqltext + 'GroupID=' + CONVERT (varchar(30), @GroupID) + ' ' ; --House number is mandatory: IF @HouseNumber IS NOT NULL AND @HouseNumber <> '' SET @sqltext = @sqltext + ' AND HouseNumber=@HouseNumber ' IF @StreetDirection IS NOT NULL AND @StreetDirection <> '' SET @sqltext = @sqltext + ' AND (StreetDirection=@StreetDirection or @StreetDirection='''') ' IF @StreetName IS NOT NULL AND @StreetName <> '' SET @sqltext = @sqltext + ' AND StreetName like @StreetName ' IF @Suffix IS NOT NULL AND @Suffix <> '' SET @sqltext = @sqltext + ' AND (Suffix=@Suffix or Suffix='''') ' --Unit is mandatory: IF @Unit IS NOT NULL AND @Unit <> '' SET @sqltext = @sqltext + ' AND Unit=@Unit ' IF @City IS NOT NULL AND @City <> '' SET @sqltext = @sqltext + ' AND (City=@City or City='''') ' IF @State IS NOT NULL AND @State <> '' SET @sqltext = @sqltext + ' AND (State=@State or State='''') ' IF @ZIP IS NOT NULL AND @ZIP <> '' SET @sqltext = @sqltext + ' AND (ZIP=@ZIP or ZIP='''') ' --CountyId is mandatory: IF @CountyID IS NOT NULL AND @CountyID <> '' SET @sqltext = @sqltext + ' AND CountyID=@CountyID ' -- Execute as an explicitly parameterized query. This will provide plan reuse for any executions of the proc -- that have the same @GroupID and the same combination of non-empty parameters. /*print @sqltext print '@ReverseLinkURL = ' + @ReverseLinkURL print '@HouseNumber = ' + @HouseNumber print '@StreetDirection = ' + @StreetDirection print '@StreetName = ' + @StreetName print '@Suffix = ' + @Suffix print '@Unit = ' + @Unit print '@City = ' + @City print '@State = ' + @State print '@ZIP = ' + @ZIP print ' @CountyID = ' + @CountyID print 'debug: ApnNumber = ' + @ApnNumber*/
New Proc WITH Blocking issues---- -------- /* ===================== Created By: David Barrs 8-13-2002 Description: Returns the properties for given group id
Usage: EXEC USP_GetMatchedMLSRecord 1,'8108','','dunn','','','austin','TX','','48453','','','','http://sef.mlxchange.com/reverselink.asp?action=reverselink' Mods: xx/xx/xxxx - who - Description 11/28/2007 - Shiny - Refactored the procedure \\\\\\ ===================== if exists (select 1 from sysobjects where name = 'USP_GetMatchedMLSRecord') drop procedure USP_GetMatchedMLSRecord grant exec on USP_GetMatchedMLSRecord to webuser */ ALTER PROCEDURE [dbo].[USP_GetMatchedMLSRecord] ( @GroupID int, @HouseNumber varchar(50), @StreetDirection varchar(50), @StreetName varchar(50), @Suffix varchar(50), @Unit varchar(50), @City varchar(50), @State varchar(50), @ZIP char(50), @FIPS varchar(10), @ApnNumber varchar(50), @AltApn varchar(50), @ParcelId varchar(50), @ReverseLinkURL varchar(200) ) AS DECLARE @sqltext nvarchar(4000), @paramlist nvarchar(4000), @CountyID char(6) Select @CountyID=CountyID from ltCounties where FIPS=@FIPS IF (@ApnNumber IS NOT NULL AND @ApnNumber <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@ApnNumber AND GroupID=@GroupID )) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@ApnNumber AND GroupID = @GroupID ; ELSE BEGIN IF (@AltApn IS NOT NULL AND @AltApn <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@AltApn AND GroupID=@GroupID)) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber= @AltApn AND GroupID=@GroupID; ELSE IF (@ParcelId IS NOT NULL AND @ParcelId <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WHERE APNnumber=@ParcelId AND GroupID=@GroupID )) SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber= @ParcelId AND GroupID=@GroupID; ELSE BEGIN -- Finalize parameter values IF @ReverseLinkURL IS NULL SET @ReverseLinkURL = ''; IF @StreetName IS NOT NULL AND @StreetName <> '' SET @StreetName = @StreetName + '%'; -- Build up SQL text dynamically, only including filter predicates for those parameters that the user wants -- to search on. SELECT @sqltext = 'Select top 100 '''' + @ReverseLinkURL as ''ReverseLinkBaseURL'',MLSNumber,Comment from tblMLSListing WITH (NOLOCK) where ' IF @GroupID IS NOT NULL SELECT @sqltext = @sqltext + 'GroupID=' + CONVERT (varchar(30), @GroupID) + ' '
SELECT @sqltext = @sqltext + ' AND HouseNumber=@HouseNumber '
IF @StreetDirection IS NOT NULL SELECT @sqltext = @sqltext + ' AND StreetDirection = @StreetDirection '
IF @StreetName IS NOT NULL SELECT @sqltext = @sqltext + ' AND StreetName LIKE @StreetName + ''%'''
IF @Suffix IS NOT NULL SELECT @sqltext = @sqltext + ' AND Suffix = @Suffix'
SELECT @sqltext = @sqltext + ' AND Unit=@Unit '
IF @City IS NOT NULL SELECT @sqltext = @sqltext + ' AND City = @City'
IF @State IS NOT NULL SELECT @sqltext = @sqltext + ' AND State = @State'
IF @ZIP IS NOT NULL SELECT @sqltext = @sqltext + ' AND ZIP = @ZIP' SELECT @sqltext = @sqltext + ' AND CountyID='+ CONVERT (varchar(30), @CountyID)+' ' SELECT @paramlist = ' @GroupID int, @HouseNumber varchar(50), @StreetDirection varchar(50), @StreetName varchar(50), @Suffix varchar(50), @Unit varchar(50), @City varchar(50), @State varchar(50), @ZIP char(50), @FIPS varchar(10), @ApnNumber varchar(50), @AltApn varchar(50), @ParcelId varchar(50), @ReverseLinkURL varchar(200)'
We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.
Code Snippet
[SqlFunction ( FillRowMethodName = "FillRow",
TableDefinition = "CustomerID nvarchar(MAX)",
SystemDataAccess = SystemDataAccessKind.Read,
DataAccess = DataAccessKind.Read,
IsDeterministic=false)]
public static IEnumerable GetWishlist () {
using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {
using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {
if (reader.Read ()) {
myList.Add ( reader[0] as string );
}
}
return (IEnumerable)myList;
}
}
When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.
Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. Basically, here's the scenario... I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML. The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security. Basically, the connection context is using security for a low-privilaged Windows account ("UserX") and UserX has no permission to the table referenced in the dynamic SQL but because of the dyanmic nature of the query, the stored procedure ends up adopting the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX. Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns. So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case? I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may be as secure, but simpler. PS - Please don't let this degenerate into a conversation about OR mappers. I know that happens a lot on these forums.
Hi, I need to create a stored procedure, which needs to accept the column name and table name as input parameter, and form the select query at the run time with the given column name and table name.. my procedure is, CREATE PROC spTest @myColumn varchar(100) , @myTable varchar(100) AS SELECT @myColumn FROM @myTable GO This one showing me the error, stating that myTable is not declared.. .............as i need to perform this type of query for more than 10 tables.. i need the stored procedure to accept the column and table as parameters.. Plese help me?? Is it possible in stored procedure..
I am taking my first steps into stored procedures and I am working on a solution for efficiently paging large resultsets with SQL Server 2000 based on the example on 4Guys: http://www.4guysfromrolla.com/webtech/042606-1.shtml The problem with my stored procedure is, is that it doesn't seem to recognize a variable (@First_Id) in my dynamic Sql. With this particular sproc I get the error message: "Must declare the scalar variable '@First_Id'"It seems to be a problem with 'scope', though I still can't yet figure out. Can anyone give me some hints on how to correctly implement the @First_Id in my stored procedure? Thanks in advance! Here's the sproc: ALTER PROCEDURE dbo.spSearchNieuws(@SearchQuery NVARCHAR(100) = NULL,@CategorieId INT = NULL,@StartRowIndex INT, @MaximumRows INT,@Debug BIT = 0)ASSET NOCOUNT ONDECLARE @Sql_sri NVARCHAR(4000),@Sql_mr NVARCHAR(4000),@Paramlist NVARCHAR(4000),@First_Id INT, @StartRow INTSET ROWCOUNT @StartRowIndexSELECT @Sql_sri = 'SELECT @First_Id = dbo.tblNieuws.NieuwsId FROM dbo.tblNieuwsWHERE 1 = 1'IF @SearchQuery IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND dbo.tblNieuws.CategorieId = @xCategorieId'SELECT @Sql_sri = @Sql_sri + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'SET ROWCOUNT @MaximumRows SELECT @Sql_mr = 'SELECT dbo.tblNieuws.NieuwsId, dbo.tblNieuws.NieuwsKop, dbo.tblNieuws.NieuwsLink, dbo.tblNieuws.NieuwsOmschrijving, dbo.tblNieuws.NieuwsDatum, dbo.tblNieuws.NieuwsTijd, dbo.tblNieuws.BronId, dbo.tblNieuws.CategorieId, dbo.tblBronnen.BronNaam, dbo.tblBronnen.BronLink, dbo.tblBronnen.BiBu, dbo.tblBronnen.Video, dbo.tblCategorieen.CategorieFROM dbo.tblNieuws INNER JOIN dbo.tblBronnen ON dbo.tblNieuws.BronId = dbo.tblBronnen.BronId INNER JOIN dbo.tblCategorieen ON dbo.tblNieuws.CategorieId = dbo.tblCategorieen.CategorieId AND dbo.tblBronnen.CategorieId = dbo.tblCategorieen.CategorieId WHERE dbo.tblNieuws.NieuwsId <= @First_Id AND 1 = 1' IF @SearchQuery IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND dbo.tblNieuws.CategorieId = @xCategorieId' SELECT @Sql_mr = @Sql_mr + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'IF @Debug = 1PRINT @Sql_mr SELECT @Paramlist = '@xSearchQuery NVARCHAR(100), @xCategorieId INT'EXEC sp_executesql @Sql_sri, @Paramlist, @SearchQuery, @CategorieIdEXEC sp_executesql @Sql_mr, @Paramlist, @SearchQuery, @CategorieId
Hi i am trying to make the "userName" section of the code below dynamic as well, how can i do this, the reason being userName will not always be passed through to it.
ALTER PROCEDURE [dbo].[stream_UserFind] ( @userName varchar(100), @subCategoryID INT, @regionID INT )ASdeclare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like ' + char(39) + '%' + @UserName + '%' + char(39) if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = ' + cast( @subCategoryID as varchar(10))if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = ' + cast( @regionID as varchar(10)) print @StaticStr exec(@StaticStr) )
Hi all, I'm using sql 2005. Can some one please tell me how to write dynamic sql for count. What i want is that i want to count the number of employees existing for the given department names and get the counted values as output into my vb.net 2.0 project. If any one know who to write this please send the code.. Please help me.. I want the below code to change to dynamic sql: Alter proc GetCountforemp @DestName varchar(200)=null, @total int output as begin SELECT @total = Count(distinct Employee.EmployeeID) FROM Employee INNER JOIN Dest R on R.DestID=Employee.DestID WHERE R.DestName in ('''+@DestName+''') end
My existing ASP 1.0 site keeps getting hacked using SQL injections. I have rewritten the site in ASP 3.5 to stop the attacks but cannot figure out how to dynamically generate a basic keyword search. I am trying to take the keywords entered into an array and then construct the WHERE clause - not having much luck. Getting either errors or double LIKE statements. Need some help. string[] SqlKWSrch; SqlSrch = KWordSrch.Text;SqlKWSrch = SqlSrch.Split(' ', ','); int AStop = SqlKWSrch.Length; int i = 0; foreach( string a in SqlKWSearch ) { if (i <= AStop) { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' AND "; } else { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' "; } i++; } 1) I can't seem to properly terminate the final LIKE statement2) can't figure out how to pass 'SqlWHR' to the procedure GIVEN KEYWORDS: 'antique chairs' entered I want to end up with the below SP, the @SqlWHR parameter appeared to have worked once but it probably was an illusion. PROCEDURE KeyWordSearch@SqlWHR varchar(100)AS SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'antique' + '%' AND L_Kwords LIKE '%' + 'chairs' + '%' AND L_Display = 1 RETURN
Hi, I have a table with values such as test1, test2, test3, test4, test5. I need to write a stored procedure with paramater (number TINYINT, number2 TINYINT), the number represents the field that I'm going to select and compare. For example if I pass in (1,5) I will need the fields test1 and test5 and store them in Temp and Temp2. How do I write the following to so it will dynamically select which field to use when passing the parameters? DECLARE @Temp TINYINT, DECLARE @Temp2 TINYINT, SELECT top 1 Temp = test1, Temp2 = test5 from table
Hi all! I need to create a stored procedure with a parameter and then send a WHERE clause to that parameter (fields in the clause may vary from time to time thats why I want to make it as dynamic as possible) and use it in the query like (or something like) this:
SELECT fldID, fldName FROM tblUsers WHERE @crit ----------------------------------------------------
Of course this does not work, but I don't know how it should be done, could someone please point me in the right direction on how to do this kind of queries.
I am trying to do something similar to the following where I want to perform dynamic ordering on two tables that have been unioned as shown below.
CREATE PROCEDURE procedure_name @regNum varchar(14), @sortOrder tinyint = 1 AS SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register', Obs_Date As 'Observation Date' FROM tblSPG_Header WHERE REG = @regNum UNION SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register', Obs_Date As 'Observation Date' FROM tblRCH_Header WHERE REG = @regNum ORDER BY Obs_Date DESC GO
Note that I am only sorting on the Obs_Date column, but I'd like to be able to sort on any column within the selection list. I know that I need to use:
ORDER BY CASE WHEN @sortOrder = 1 THEN Obs_Date END DESC
but I frequently get the following error when I try to do so:
"ORDER BY items must appear in the select list if the statements contain a UNION operator"
If anyone can offer any suggestions, I would appreciate it. Thanks.
Hi, I have several parameters that I need to pass to stored procedure but sometimes some of them might be null. For example I might pass @Path, @Status, @Role etc. depending on the user. Now I wonder if I should use dynamic Where clause or should I use some kind of switch, maybe case and hardcode my where clause. I first created several stored procedures like Documents_GetByRole, Documents_GetByRoleByStatus ... and now I want to combine them into one SP. Which approach is better. Thanks for your help.
DECLARE @RowCount int SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'
if @RowCount > 0 BEGIN
SELECT z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear, a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance /* The above functions requires the Distance Assistant. */ FROM ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a WHERE z.Latitude <= r.MaxLat AND z.Latitude >= r.MinLat AND z.Longitude <= r.MaxLong AND z.Longitude >= r.MinLong AND z.CityType = 'D' AND z.ZIPCodeType <> 'M' AND z.ZIPCode = a.Zipcode AND a.AdActive = '1' AND a.AdExpiredate >= getdate() AND a.Make = @Make AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles /* The above functions requires the Distance Assistant. Also note that SQL Server caches the results so that this and the "SELECT dbo.DistanceAssistant" functions are both only computed once. */ ORDER BY Distance, Make
END ELSE SELECT -1 As ZIPCode --ZIP Code not found...
....................
This stored procedure work very well.
The question is how I add some dynamic condition inside the where condition.
I want to add:
If @Model <> "See All Models" AND a.Model = @Model'
If @AutoType <> "New/Used" AND a.Condition = @AutoType
I try several ways, but fail.
If you know how to add these two dynamic parameters into the condition of stored procedure, please help.
I am in the very final stages of building a dating app for a client, I am totally stuck with the advanced search page. been googling for days with limited success
For the most basic of purposes I have added a few form fields to my search.aspx page; county (Dropdown list) min age (Dropdown list) max age (dropdown list) Smoker (check box) keyword (textbox)
My codebehind passes the vars to a stored procedure @county = me.county.selectedvalue etc My problem is the stored procedure I sort of have the following but I can't get it to run <code> ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH (@countyID int , @MaxAge varchar(100), @MinAge varchar(100),
I have a stored procedure being called based on user search criteria. Some, the colour and vendor fields are optional in the search so i do not want that portion of the procedure to run.
at this point i keep getting errors in the section bolded below it never seems to recognize anything after the if @myColours <> 'SelectAll'
CREATE Procedure PG_getAdvWheelSearchResults3 ( @SearchDiameter NVarchar( 20 ), @SearchWidth NVarchar( 20 ), @minOffset int , @maxOffset int , @boltpattern1 NVarchar( 20 ), @VendorName NVarchar( 40 ), @myColours NVarchar( 40 ) ) As BEGIN TRANSACTION SELECT *, dbo.VENDORS.*, dbo.WHEEL_IMAGES.Wheel_Thumbnail AS Wheel_Thumbnail, dbo.WHEEL_IMAGES.Wheel_Image AS Wheel_Image, dbo.WHEELS.*, dbo.VENDOR_IMAGES.Vendor_Thumbnail AS Expr1, dbo.VENDOR_IMAGES.Vendor_AltTags AS Expr2 FROM WHEEL_CHARACTERISTICS INNER JOIN dbo.VENDORS ON WHEEL_CHARACTERISTICS.Vendor_ID = dbo.VENDORS.Vendor_ID INNER JOIN dbo.WHEEL_IMAGES ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEEL_IMAGES.Wheel_ID INNER JOIN FILTER_CLIENT_WHEELS5 ON WHEEL_CHARACTERISTICS.Wheel_ID = FILTER_CLIENT_WHEELS5.Wheel_ID INNER JOIN dbo.WHEELS ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEELS.Wheel_ID INNER JOIN CLIENT_WHEEL_PRICES5 ON FILTER_CLIENT_WHEELS5.Client_ID = CLIENT_WHEEL_PRICES5.ClientId AND WHEEL_CHARACTERISTICS.Wheel_Char_ID = CLIENT_WHEEL_PRICES5.Wheel_Char_ID INNER JOIN dbo.VENDOR_IMAGES ON dbo.VENDORS.Vendor_ID = dbo.VENDOR_IMAGES.Vendor_ID WHERE (dbo.VENDORS.Vendor_Active = 'y') AND (FILTER_CLIENT_WHEELS5.FCW_Active = 'y') AND (FILTER_CLIENT_WHEELS5.Client_ID = '1039') AND (WHEEL_CHARACTERISTICS.Wheel_Diameter =@SearchDiameter) AND (WHEEL_CHARACTERISTICS.Wheel_Width =@Searchwidth) AND (WHEEL_CHARACTERISTICS.Wheel_Bolt_Pattern_1 = @boltpattern1)
if @myColours <> 'SelectAll' and WHEEL_CHARACTERISTICS.Wheel_Search_Colour = @myColours end if
AND (cast(WHEEL_CHARACTERISTICS.wheel_Offset as int(4)) BETWEEN @minOffset AND @maxOffset)
ORDER BY CLIENT_WHEEL_PRICES5.Price asc COMMIT TRANSACTION GO
Anyone know how i should word the if...statements? I have not found anything that works yet. Thanks
