Hi,
Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling
a SELECT query from another SP??
Something like...
CREATE procedure spThisSP(@param varchar(20))
AS
SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @param)
Cheers
Hi, I have an sql query like this :DECLARE @TableName varchar(200), @ColumnName varchar(200), @EmployeeID varchar(200), @Result varchar(200);SET @TableName = 'Customer';SET @ColumnName = 'First_Name';SET @CustomerID = 28;-- This line return ErrorSET @Result = EXEC ('select' + @ColumnName + ' from ' + @TableName + ' where Recid = ' + @CustomerID + '');Print @Result; I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC? Thanks
View 1 Replies View RelatedOK, I'm fairly new to SQL Server, but I know SQL and databases pretty well. I'm just starting to use the dynamic SQL feature of SQL Server (with EXEC), and am wondering how to return a scalar value from a dynamic SQL expression. I realize I can't use EXEC in a user-defined function, but I want to create a stored procedure with one OUTPUT variable so I can simulate a function. The following code does not work, because EXEC does not return a value:
CREATE PROCEDURE dbo.ExecFunction ( @ScalarSELECTString varchar(250), @@ReturnVal sql_variant )
set @@ReturnVal = ( exec @ScalarSELECTString )
go
So, I was wondering if someone might be able to suggest a way to re-write the above code to achieve the same effect. Thanks in advance.
ALTER PROCEDURE [dbo].[GD_SP_FACTURA_GLOBAL]
AS
DECLARE @DIR nvarchar(10)
SELECT @DIR = DIR_NOME FROM Direccao
EXECUTE dbo.GD_SP_FACTURA_ValorTotal @DIR
I Have the query above, It's correct, but I want the SUM of all the values returned by EXECUTE function.
Thanks
Hi
I have a maintenance proceudre that trims data tables on the live site, but prior to trimming i need to execute procs via linked servers at four seprate locations (subscribers) to ensure all of their data is up to date before the trim happens (the deletes are blocked at the subs). my problem is that if i go
exec @error_id = procname
and an error such as permission being denied or proc missing, the return code
is still 0 - therefor it looks like th proc ran successfully, meanwhile it didnt.
does anyone know how to check for these sorts of erors, so i dont leave this to chance?
thnks
des
Hi,
I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.
This is my function:
ALTER FUNCTION [dbo].[ReturnPickItemValue]
(
-- Add the parameters for the function here
@TypeID int,
@CaseID int
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @RTN varchar(max)
IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @TypeID) = 1
BEGIN
SET @RTN = 'SELECT PickItem I +
CASE D.IsStartDateEstimated
WHEN 0 THEN CAST(StartDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)
END +
CASE D.IsEndDateEstimated
WHEN 0 THEN CAST(EndDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)
END
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID
LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID
WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + '
AND P.CaseID = ' + CAST(@CaseID as varchar)
END
ELSE
BEGIN
SET @RTN=
'SELECT I.PickItem
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID
WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + '
AND CaseID = ' + CAST(@CaseID as varchar)
END
RETURN @RTN
END
Each time I try " RETURN EXEC(@RTN) " or something similar I get an error.
I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.
Anyone with any ideas?
hello world how are there
View 1 Replies View RelatedI use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]
[Code] ....
How do I use a @variable to hold on value return from an exec ('string command') statement.
Example for:
declare @OldID int
declare @cmd varchar(255)
declare @db varchar(25)
declare @OldOwner varchar(25)
set @db = 'DBNAME'
set @OldOwner = 'USERNAME'
select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
exec (@cmd)
How can I use @OldID to hold on UID return from this statement.
When I try use:
select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
then I'm getting a error message: @OldID not declare.
Thanks.
Hello everybody!
As the topic:
Can i get the value "count(*)" from EXEC('select count(*) from xTable')
Any helps will be usefull! Thanks!
I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT
---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO
Thanks in advance
Dave
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
Greeting.
I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.
I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.
Have this problem solution? May be, I must use another method to get my data?
P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.
P.S.S. Sorry for bad english.
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
View 7 Replies View RelatedI know this table is designed wrong for what I am doing but I hope Ican do it. I have a table like this.Prod_A_Jan, Prod_A_Feb, Prod_B_Jan, Prod_B_FebI want a query that returns data like this (two rows of data)"ProdA", Prod_A_Jan, Prod_A_Feb"ProdB", Prod_B_Jan, Prod_B_FebI know two queries can get it but I want one. Any Help would begreat!!!Sheila T.
View 3 Replies View RelatedHI All,
I need help in sp. I have the sp that return the result but then i also want count how many rows are the result. Does anyone know how to do that?
This is my sp, and when it returns the data i also want it to return the how many rows are the result.
CREATE procedure dbo.ph_getphonebookoption
@country nvarchar(100),
@company nvarchar(100),
@office nvarchar(100)
as
select
Ext,
FName,
LName,
Title
from
phonebook
where country =@country
and company=@company
and office =@office
select count (*)
GO
Trying to do a paging scheme without using #Temp tables in MS SQL 7.0
Client calls a sp passing 1 and sql returns the first 100 records.
Client sends a sp passing 100 and gets the next 100 records.
Process continues till @@fetch_status <> 0 or the client can stop sending requests.
I implemented it easily using fetch absolute into a #temp table but this has dissaster potential in a multiuser environment since everyone will be using this query continously and there is no user limit.
What other options do I have?
Thanks,
John
is there is any way to find out whether my query return 0 rows inside a stored procedure.
View 2 Replies View RelatedHi All,
I have a question,
Select top 10 * from employee
the above statement return top 10 row.
but i want the rows from the table other than the top 10. Can any one help me to get it.. iam using SQL server 2005
Thanks for the help
Thanks
Bhaskar
hi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help
SELECT SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4FROM TABLE1WHERE (ID = nn) UNION all
SELECT SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3FROM table2WHERE TABLE2 = nn)
I have a database that has 100,000+ records in a table. Am I better off returning all of the records from a search even if it is 50,000 records or is it better to do a SELECT COUNT(*) And nested SELECT TOP x statements to only return 1 page of results? What is the best practice for a situation like this?
SELECT * FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another.... AND...
OR
SELECT COUNT(*) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @OrderAnd
SELECT TOP(@pagesize) FROM (SELECT TOP(@pagesize * @pagenum) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @Order)ORDER BY @revOrder
What is the easiest way to return rows 200 through to 300 of a 500 row result set using SQL? Is there a simple way of doing this or do I need to write some Transact SQL? Any ideas would be appreciated.
Thanks
Rod
Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data?
The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.
Hi.
How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?
thanks
The following query returns 2142 rows which is correct.
Code:
select COUNT(*), sum(v.currentMarket)
from TRMaster m
inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel
inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A'
where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1
group by m.Year, m.Parcel
having SUM(v.currentmarket) > 0
How can I convert this query so that it returns just the count of 2142?
I have a Dataset that I am populating from a SQL Query. I am then using the dataset to populate a report in Reporting Services. What I want to do is return a standard number of rows in my dataset. (Let's say 10.) Even if my query does not have any rows in it, I want 10 empty rows returned to the dataset. If my query has 7 rows in it then I want to add on 3 empty rows and return it. I will not have more than the standard number of rows.
I cannot get the table in the report to show up if the dataset is empty, but still want the table to display with 10 empty rows. I have searched how to do this online but am getting nowhere. (I know how to add one empty row but not a set number.
Hello All
Please can anyone advice me how I can fetch list of all tables which have no records in it in sql server
Thanks
Hi.How does one return a range of rows.I know that "Top 5" will return rows 0 - 5but, how do I get 6 - 10?thanks
View 17 Replies View RelatedHow can a SQL statement be written to return a specified range ofrows? For example:-- tblContact-- (-- SSN char(9),-- FirstName varchar(50),-- LastName varchar(50)-- )-- This table contains 500 rows.Select * from tblContact -- Return only rows 5 through 10Thanks
View 2 Replies View RelatedAre there any way to execute a procedure and return N random rows?
I have a charting control that is fed from an SqlDataSource. How do I set the SqlDataSource to only return the top N records where N is set by a web form control?The user should have the option to chart the top 3, 5, or 10 items.Is the best approach to switch to an ObjectDataSource?
View 3 Replies View RelatedI am in need of help to develop a query
I have two tables Exams and Exams_lab, that are joined by a field id_exame. I want to return The Exams that has all the dependent rows in Exames_lab with the same value in the status_int field of Exames_lab. Can anyone Help Me?
Lets see an example
tb_exame
id_exame
date_exame
1
07/01/2006
2
08/01/2006
3
09/01/2006
tb_exame_lab
id_exame_lab
id_exame
desc_exame
status_exame
1
1
NORMAL
1
2
1
0
3
2
NORMAL
1
4
2
PROBLEMS
1
5
2
OK
1
6
3
OK
0
in this exemple my query must return only id_exame 2 and 3 because id_exame 1 has two different values on id_status on tb_exame_lab
can anyone help me?
Can someone look at this and tell me where I went wrong? I'm trying to return all duplicate rows that have the same lastName and Address. It returns rows but they don't look like dups.SELECT TOP (100) PERCENT dbo.tblClient.LastName, dbo.tblClientAddresses.Address
FROM dbo.tblClient INNER JOIN
dbo.tblClientAddresses ON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_ID
GROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.Address
HAVING (COUNT(dbo.tblClientAddresses.Address) > 1)
ORDER BY dbo.tblClientAddresses.Address