Sorting Currency In A Stored Proc
Nov 12, 2003
I'm brain-dead so forgive the simple question, but how do you sort a table based on the value in a money datatype field when sorting defaults to:
$1
$10
$15
$2
$20
$25
...
$5
$55
etc.
Obviously this isn't a "sorted by total amount", where I'd want the results returned like:
$1
$2
$3
...
$9
$10
$11
...
etc.
Links to examples or documentation would be great (I've tried searching both these forums and Google already).
View 8 Replies
ADVERTISEMENT
Feb 13, 2008
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
View 3 Replies
View Related
Aug 24, 2006
I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
View 3 Replies
View Related
Jun 15, 2006
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies
View Related
Feb 23, 2007
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies
View Related
Feb 20, 2003
I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
View 1 Replies
View Related
Jan 13, 2006
Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
View 5 Replies
View Related
Jan 20, 2004
Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
View 14 Replies
View Related
Aug 30, 2007
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
View 2 Replies
View Related
Jul 5, 2006
Hi, i'm trying to enable custom paging and sorting in my grid view control. The trouble i'm getting is the sorting. I have paging working fine with the following code:
WITH NewsEntries AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY fldTitle ASC) AS RowNum
FROM qryNews
)
SELECT *
FROM NewsEntries
WHERE RowNum BETWEEN @StartRowIndex + 1 AND @StartRowIndex + @MaximumRows
I tried replacing fldTitle ASC with @SortBy and passing that it but when i execute the query to test it doesn't do any sorting. After reading an article on a website i now realize i have to put:
DECLARE @Sql nvarchar(4000)
SET @Sql = 'SELECT *
FROM
(SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @SortBy + ') AS RowNum
FROM qryNews
WHERE fldTitle LIKE ''%' + @SearchBy + '%''
) AS NewsEntries
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @StartRowIndex) + ' + 1 AND
' + CONVERT(nvarchar(10), @StartRowIndex) + ' + ' + CONVERT(nvarchar(10), @MaximumRows)
-- Execute the SQL query
EXEC sp_executesql @Sql
and it works fine when i execute it sql server management studio but when i run my page with the grid view on i get the following error:
Incorrect syntax near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ')'.Source Error:
Line 4920: End If
Line 4921: Dim dataTable As KIT.tbdNewsDataTable = New KIT.tbdNewsDataTable
Line 4922: Me.Adapter.Fill(dataTable)
Line 4923: Return dataTable
Line 4924: End FunctionSource File: C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
ootec31cfa3e7f364f7App_Code.jtrijhok.1.vb Line: 4922 Stack Trace:
[SqlException (0x80131904): Incorrect syntax near ')'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857306
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734918
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
System.Data.SqlClient.SqlDataReader.get_MetaData() +62
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +141
System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +162
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +107
KITTableAdapters.tbdNewsTableAdapter.GetNewsSubset(String SearchBy, String SortBy, Nullable`1 StartRowIndex, Nullable`1 MaximumRows) in C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
ootec31cfa3e7f364f7App_Code.jtrijhok.1.vb:4922
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0
System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +358
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29
System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +482
System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2040
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +24
System.Web.UI.Control.PreRenderRecursiveInternal() +77
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
Appreciate the help. Thanks
View 1 Replies
View Related
Mar 10, 2008
I have the following, which loads up a product search i now want to be able to sort it based on criteria such as price. This is what i have so far; String str = Session["subCategoryID"].ToString();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand command = new SqlCommand("stream_CategoryResults", conn);
command.Parameters.Add("@subCategoryID", SqlDbType.Int).Value = str;command.CommandType = CommandType.StoredProcedure;
conn.Open();SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
dgProducts.DataSource = reader;
dgProducts.DataBind();
conn.Close();
My question is would i need to have different stored procedures, for when loading it up, and when sorting it by price. This is what my stored procedure looks like;CREATE PROCEDURE stream_CategoryResultsByPrice
@subCategoryID INT
AS
SELECT DISTINCT Companies.companyName, Companies.companyLogo,SubCategories.subCategoryName, Products.productPrice, Products.productInfoURL FROM Companies INNER JOIN Products ON Companies.companyID = Products.companyID INNER JOIN SubCategories ON Products.subcategoryID = SubCategories.subCategoryID WHERE SubCategories.subCategoryID=@subCategoryID
View 3 Replies
View Related
Dec 16, 2003
Normally when I do a sort for a stored procedure I use something like this:
ORDER BY
CASE WHEN @SortOrder = 'FirstName' THEN FirstName
WHEN @SortOrder = 'LastName' THEN LastName
WHEN @SortOrder = 'Extension' THEN Extension
ELSE LastName
How do I get that into a dynamic SQL statement with proper quotes?
Most of the SQL left out for cleanliness. The order by below would be the ELSE condition.
CREATE PROCEDURE view_by_company
@entrytype nvarchar(50),@strYears nvarchar(20), @company nvarchar(20)
AS
Declare @SQL nVarchar(4000)
Select @SQL = 'SELECT Media_Tracking_Companies.COMPANY '
Select @SQL = @SQL + 'WHERE (LTRIM(STR(DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN ('
Select @SQL = @SQL + @strYears + '))'
Select @SQL = @SQL + 'ORDER BY dbo.Media_Tracking_Ad_History.PUBLICATION, dbo.Media_Tracking_Publications.AdDate DESC '
exec (@sql)
GO
View 1 Replies
View Related
Aug 2, 2004
Hi I am trying to achieve something like:
ALTER PROCEDURE dbo.GetShares
@SortValue varChar(30)= SHARE_DESCRIPTION
as
SELECT SHARES.SHARE_DESCRIPTION, SHARES.SHARE_SYMBOL, SECTORS.SECTOR_NAME
FROM SHARES INNER JOIN
SECTORS ON SHARES.SECTOR_ID = SECTORS.SECTOR_ID
ORDER BY @SortValue
but it does not seem to be possible to use variable after order by
is there any way to achieve something with sorting by variable?
View 1 Replies
View Related
Mar 19, 2008
Hi All,
I have a field in a table that is varchar. The field can contain numbers or alpha characters. I have a view against this table and I am trying to sort on the output of this field and am running into some problems.
I am trying to use 'isnumeric' to determine if the field contains numbers and if so, then I am using 'convert' to change it to an integer so I can sort it properly. I am using a CASE statement but I am encountering a conversion error on the alpha characters. I am new to SQL and my syntax may be wrong.
Can anyone help please?
SELECT TOP 100 PERCENT SpaceName, CASE WHEN isnumeric(RoomTable.RoomNumber) <> 0 THEN CONVERT(int, RoomTable.RoomNumber)
ELSE RoomTable.RoomNumber END RoomNumber
FROM dbo.RoomTable
ORDER BY SpaceName, RoomNumber
View 5 Replies
View Related
Feb 11, 2007
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
Any suggestions would help.
Thanks
View 1 Replies
View Related
Jul 10, 2015
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. Â When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. Â The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. Â The sorting is implemented using the Category Group sorting option.
View 6 Replies
View Related
Feb 19, 2007
I have a stored procedure in my SQL 2005 Server database named Foo that accepts two parameters, @paramA and @paramB.In my ASP.NET page, I have these:<asp:GridView id="gv" runat="server" AutoGenerateColumns="true" DataSourceID="DS" AllowSorting="true" DataKeyNames="ID"/><asp:SqlDataSource ID="DS" runat="server" ConnectionString="<%$ ConnectionStrings:CS1 %>" SelectCommand="Foo" SelectCommandType="StoredProcedure" OnSelecting="DS_Selecting"> <asp:Parameter Name="paramA" Type="String" /> <asp:Parameter Name="paramB" Type="String" /></asp:SqlDataSource>In my setup, paramA and paramB are set in DS_Selecting(), where I can access the Command.Parameters[] of DS.Now, here's the problem. As you can see, the GridView allows for sorting. When you click on a header title to sort, however, the GridView becomes empty. My question is, how can I get the GV sorted and in the correct direction (i.e. asc/desc)? My first step in my attempt was to add another parameter to the SqlDataSource and sotred procedure Foo (e.g. @SortByColumn), then changed Foo appropriately: ALTER PROCEDURE Foo @paramA nvarchar(64), @paramB nvarchar(64), @SortColumn nvarchar(16) = 'SearchCount' AS SELECT * FROM Searches ORDER BY CASE WHEN @SortColumn='SearchCount' THEN SearchCount WHEN @SortColumn='PartnerName' THEN PartnerName ELSE ID ENDThat works find and dandy. But wait--I want to get the correct ORDER BY direction too! So I add another parameter to the SqlDataSource and Foo (@SortDirection), then alter Foo: ... SELECT * From Searchces ORDER BY CASE /* Keep in mind that CASE short-circuits */ WHEN @SortColumn='SearchCount' AND @SortDirection='desc' SearchCount DESC WHEN @SortColumn='SearchCount' SearchCount WHEN @SortColumn='PartnerName' AND @SortDirection='desc' PartnerName DESC WHEN @SortColumn='PartnerName' PartnerName WHEN @SortColumn='ID' AND @SortDirection='desc' ID DESC ELSE ID END ...But including DESC or ASC after the column name to sort by causes SQL to error. What the heck can I do, besides convert all my stored procedures into in-line statements inside the ASP page, where I could then dynamically construct the appropriate SQL statement? I'm really at a loss on this one! Any help would be much appreciated!Am I missing a much simpler solution? I am making this too complicated?
View 2 Replies
View Related
Mar 19, 2001
I want to sort a stored procedure based on a variable passed to it... what is the easiest way to do this? Here's an example of what I want to do:
sp_select_thing 10, 'thing_name', 'asc'
It would run the query then somehow evaluate parameters 2 and 3 to be placed in the ORDER BY clause. I'm not sure if they should be quoted as strings or not, I don't have an idea how to pass a "reference to a variable" as a parameter to a stored procedure... or even if such a thing is possible
Thanks!
View 1 Replies
View Related
Oct 14, 2007
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies
View Related
May 6, 2007
As I said above, how do I put sorting + paging in a stored procedure.My database has approximately 50000 records, and obviously I can't SELECT all of them and let GridView / DataView do the work, right? Or else it would use to much resources per one request.So I intend to use sorting + paging at the database level. It's going to be either hardcode SQL or stored procedures.If it's hardcode SQL, I can just change SQL statement each time the parameters (startRecord, maxRecords, sortColumns) change.But I don't know what to do in stored procedure to get the same result. I know how to implement paging in stored procedure (ROW_NUMBER) but I don't know how to change ORDER BY clause at runtime in the stored procedure.Thanks in advance.PS. In case "ask_Scotty", who replied in my previous post, http://forums.asp.net/thread/1696818.aspx, is reading this, please look at my reply on your answer in the last post. Thank you.
View 3 Replies
View Related
Apr 28, 2008
This is for SQL Server 2000. The purpose of the procedure is to return a subset of a filtered and sorted result set. The subset, filter criteria, and sort column and sort direction can be set dynamically. It uses the rowcount technique for paging.
This would be used to drive an ASP.NET gridview which supports filtering of the data, paging, and sorting. Please let me know what improvements I can make or if you have an idea for a better solution. (I didn't put this in a vBulletin code block because personally I find two sets of scroll bars annoying, but I can if people think it's better).
CREATE PROCEDURE dbo.Books_GetFilteredSortedSubset
(
-- paging
@startRowIndex INT = 1,
@maximumRows INT = 999999,
-- sorting
@sortColumn NVARCHAR(30) = 'title_id',
@sortDirection NVARCHAR(4) = 'ASC',
-- filtering
@title VARCHAR(100) = NULL,
@type VARCHAR(30) = NULL,
@price MONEY = NULL
)
AS
BEGIN
DECLARE @sql NVARCHAR(4000)
DECLARE @parameters NVARCHAR(4000)
DECLARE @tableSource NVARCHAR(4000)
DECLARE @orderByExpression NVARCHAR(4000)
DECLARE @searchCondition NVARCHAR(4000)
DECLARE @uniqueKey NVARCHAR(30)
-- set the unique key used to ensure the rows are sorted deterministically
SET @uniqueKey = 'title_id'
-- build the FROM table source used throughout this procedure
SET @tableSource = 'titles t
inner join publishers p on t.pub_id = p.pub_id'
-- build the WHERE search condition used to control filtering throughout this procedure
SET @searchCondition = '(1 = 1)'
IF @title IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (title LIKE ''%' + @title + '%'')'
IF @type IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (type LIKE ''' + @type + '%'')'
IF @price IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (price = ' + CAST(@price AS NVARCHAR) + ')'
-- build the ORDER BY expression used to control the sorting throughout this procedure
SET @orderByExpression = @sortColumn + ' ' + @sortDirection
-- add uniqeKey to ORDER BY statement to ensure consistent ordering of results when @sortColumn is not unique
IF @sortColumn <> @uniqueKey
SET @orderByExpression = @orderByExpression + ', ' + @uniqueKey + ' ' + @sortDirection
-- Get the column value at the position specified by @startRowIndex when the results are sorted in the desired sort order
SET @sql = 'SET ROWCOUNT @rowcount; SELECT @start_row = ' + @sortColumn + ', @start_row_id = ' + @uniqueKey +
' FROM ' + @tableSource +
' WHERE ' + @searchCondition + ' ORDER BY ' + @orderByExpression
PRINT @sql
SET @parameters = '@rowcount INT, @start_row sql_variant OUTPUT, @start_row_id sql_variant OUTPUT'
DECLARE @start_row sql_variant
DECLARE @start_row_id sql_variant
EXEC sp_executesql @sql, @parameters, @rowcount = @startRowIndex, @start_row = @start_row OUTPUT, @start_row_id = @start_row_id OUTPUT
-- Get the filtered subset of results
-- add sql to filter the results based on criteria passed in as parameters
SET @sql = 'SET ROWCOUNT @rowcount; ' +
'SELECT
t.title_id,
t.title,
t.price,
t.type,
p.pub_name,
p.city,
p.state,
p.country
FROM ' + @tableSource +
' WHERE (' + @searchCondition + ') AND '
-- add sql to control the starting row
IF @sortDirection = 'ASC'
SET @sql = @sql + '( (' + @sortColumn + ' > @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' >= @start_row_id) )'
ELSE
SET @sql = @sql + '( (' + @sortColumn + ' < @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' <= @start_row_id) )'
-- add sql to control the ordering of everything
SET @sql = @sql + ' ORDER BY ' + @orderByExpression
PRINT @sql
SET @parameters = '@rowcount INT, @start_row sql_variant, @start_row_id sql_variant'
EXEC sp_executesql @sql, @parameters, @rowcount = @maximumRows, @start_row = @start_row, @start_row_id = @start_row_id
-- Reset the rowcount for others
SET ROWCOUNT 0
END;
GO
View 14 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Dec 18, 2007
Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
View 3 Replies
View Related
Aug 23, 2007
When you create a Stored procedure, is it automatically stored under the default Filegoup?
How can I see under which Filegroup my Stored Procedures and Triggers are stored?
View 2 Replies
View Related
Mar 10, 2005
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
View 2 Replies
View Related
Jul 19, 2007
Im trying to perform an update with a stored procedure thats all working but
an exception is thrown ....violation of primary key contraint....cannot insert duplicate pri key
I understand whats going on but how do you update some ones details if its protected this way.
?
View 3 Replies
View Related
Feb 22, 2008
I have a stored procedure , where i want to return identity column after insert but before insert i want to check if the record exist then select its identity value to return , after select statement it is retutrning null CREATE PROCEDURE SP_Attendance1 (@DIVISIONID int,@EMPLOYEEID int,@CALLDATE datetime,@RECEIVEDDATE datetime,@DOC datetime,@SYSTEMNAME VARCHAR(20),@DELETED int,@attendanceID int output) AS---DECLARE @ID intIF EXISTS (SELECT ID FROM TBLATTENDANCE WHERE EMPLOYEEID = @EMPLOYEEID AND YEAR ( CALLDATE ) = YEAR ( @CALLDATE)AND MONTH (CALLDATE) = MONTH ( @CALLDATE) AND DAY (CALLDATE) = DAY ( @CALLDATE) )BEGIN SET @attendanceID = SCOPE_IDENTITY()END ELSE BEGIN INSERT INTO TBLATTENDANCE (DIVISIONID ,EMPLOYEEID ,CALLDATE , RECEIVEDDATE ,DOC,SYSTEMNAME ,DELETED )VALUES (@DIVISIONID ,@EMPLOYEEID ,@CALLDATE , @RECEIVEDDATE ,@DOC,@SYSTEMNAME ,@DELETED ) ;SELECT DIVISIONID, EMPLOYEEID, CALLDATE, RECEIVEDDATE, DOC, SYSTEMNAME, DELETED,ID FROM TBLATTENDANCE WHERE (ID = SCOPE_IDENTITY())SELECT @attendanceID = SCOPE_IDENTITY()ENDGO Kindly help with this
View 5 Replies
View Related
Jan 15, 2004
Hi,
I am trying to create a stored proc, that delivers a recordset, per the user requirements BUT,
I want to create a Geneirc search Proc that can handle a few criteria
I was wondering if it is possible to create a VB like Select case
depending on Information supplied to the stored proc
i.e
@Loc_Thing
@Loc_OtherThing
SELECT FirstName,LastName,CIty,Job,,Company,Webpage FROM RECORDSET WHERE
Select case @Loc_Thing
Case "Mickey"
LastName = @Loc_OtherThing
Case "Walt"
Company = @Loc_OtherThing
...... etc
is it possible to create a strored proc like this?
I have found a Select case in SQL, but it doesn't work I would like it?
Any Idea's?
View 1 Replies
View Related
Nov 29, 2004
Hello,
So I created my first SP today which returned data to populate a datagrid. Worked to perfection. Now I'm trying to do a simple login and I'm having a hard time getting it together. I want the user to enter in a username and pass then if user exists, to return their userid and update their last login date. But I can't get it. Any help would be awesome.
Here's my SP...
CREATE PROCEDURE [dbo].[userLogin] @username varchar(50), @pass varchar(50) AS
declare @x int
declare @userid int
if exists (SELECT userid FROM users WHERE username = @username AND password = @pass)
set @x = 1
else
set @x = 0
if @x = 1
UPDATE users SET lastlogin = getdate() WHERE userid = (SELECT userid AS name FROM users WHERE username = @username AND password = @pass)
else
return '0'
if @x = 1
SET @userid = (SELECT userid FROM users WHERE username = @username AND password = @pass)
return @userid
GO
Any constructive criticism on my SP is welcome. Also, should I be using Print or Return if I want to send back a value to my VB code?
And my VB:
Dim user As String = txtUsername.Text
Dim pass As String = txtPassword.Text
Dim objDataSet As DataSet
Dim objAdapter As SqlDataAdapter
Try
Dim cmd As New SqlCommand
'Enter Param's here
Dim myParam1 As SqlParameter = cmd.Parameters.Add("@username", SqlDbType.VarChar)
Dim myParam2 As SqlParameter = cmd.Parameters.Add("@pass", SqlDbType.VarChar)
myParam1.Value = user
myParam2.Value = pass
objAdapter = New SqlDataAdapter
objAdapter.SelectCommand = cmd
objAdapter.SelectCommand.Connection = SqlConn
objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
objAdapter.SelectCommand.CommandText = "userLogin"
SqlConn.Open()
Dim str As String = cmd.Parameters("@userid").Value()
cmd.ExecuteNonQuery()
If str = "0" Then
'Error Login Page
Response.Redirect("login_err.aspx")
Else
Session("userid") = str
Response.Redirect("home.aspx")
End If
SqlConn.Close()
Catch ex As SqlException
SqlConn.Close()
End Try
View 4 Replies
View Related
Feb 13, 2006
I have 2 tables, lets say: Table1 and Table2.
Table1 has an ID field that is unique (PK).
Table2 has the same ID field and a date field which are both
unique (PK, FK)
I need to write a stored procedure that will query the ID
field from table1 like
Select distinct(id) from Table1
Would return:
ID
1
2
3
4
5
Now on the first of every month I want a job to run that
would create a new record for EACH ID from the above query. The new record would be created in Table2
with the ID and a date (that I will determine), resulting in:
ID DATE
1 01/01/06
2 01/01/06
3 01/01/06
4 01/01/06
5 01/01/06
The following month, after running it again, the table would
look like:
ID DATE
1 01/01/06
1 02/01/06
2 01/01/06
2 02/01/06
3 01/01/06
3 02/01/06
4 01/01/06
4 02/01/06
5 01/01/06
5 02/01/06
I have no idea how to “loop� through this record set for
each record in Table1 and in turn insert into Table2.
View 2 Replies
View Related
Mar 30, 2006
I have a current stored proc that creates records based on
certain criteria. One of the fields I
have is a SmallDateTime field. To
populate this from my stored proc, I have this code (for this one field).
SELECT @myLeaveDate = CAST(STR(MONTH(getdate()))+'/'+STR(01)+'/'+STR(YEAR(getdate())) AS DateTime)
This always creates a record for the 1st of the
current month. This works fine as is. After it runs I can look at the table and it
creates dates that look like the following: “2/1/2006� -Notice it doesn’t have
any minutes, seconds, etc.
Now what I need is to do something similar in another field
which is also SmallDateTime, BUT I want the date to be for the 10th day
of the following month.
I got this working using dateadd, but it also appends the minutes, seconds,
etc.
View 1 Replies
View Related
May 9, 2001
Can I call a sp from within a sp ?
Is this a fairly normal practice ?
Thanks,
Ivan
View 2 Replies
View Related
Sep 15, 1999
Can someone help us with this stored proc.
We need the maxccid -1 to be passed as a value.
Here is the proc.
CREATE PROCEDURE NewBillingInfo
@int_acct_id char (10)
@cc_nickname varchar (20),
@cc_zip varchar (20),
@cc_name varchar (100),
@cc_num varchar (20),
@cc_typ varchar (20),
@cc_month char (2),
@cc_year char (4)
as
set nocount off
declare @maxaddrid int
declare @maxccid smallint
/* BEGIN ENCRYPT DATA*/
exec encrypt @cc_zip output, @cc_zip
exec encrypt @cc_name OUTPUT, @cc_name
exec encrypt @cc_num OUTPUT , @cc_num
exec encrypt @cc_typ OUTPUT, @cc_type
exec encrypt @cc_month OUTPUT, @cc_month
exec encrypt @cc_year OUTPUT, @cc_year
BEGIN TRAN
/* get max credit card id for customer and add 1 */
SELECT @maxccid = Max(int_cc_id) from CusCredit where int_acct_id = @int_acct_id
if @maxcced is null
SELECT @maxccid = -1
SELECT @maxccid = @maxccid + 1
INSERT INTO CusCredit
(int_acct_id, int_cc_id, cc_name, cc_num,
cc_typ, cc_month, cc_year, cc_zip, cc_nickname)
VALUES
(@int_acct_id, @maxccid, @cc_name, @cc_num,
@cc_type, @cc_month, @cc_year, @cc_zip, @cc_nickname)
if @@ROWCOUNT < 1
BEGIN
ROLLBACK TRAN
RETURN 103
end
COMMIT TRAN
RETURN
GO
If someone could take a minute and look at this I would be so grateful.
Thanks Dianne Watson
View 1 Replies
View Related
