Using Stuff To Return A String Of Values From A Column Without A Cursor

Dec 13, 2007

I think it was Pat Phelan who posted a little trick here where he used the STUFF function to create a string fo values from a column without using a cursor.

I am starting a brand new project and I did my table design and I am awaiting a finalized requirements document to start coding and I thought I would spend a little time writing some code to autogenerate some generic one record at a time SELECT, INSERT,UPDATE and DELETE stored procedures. With the coming holiday things are getting quiet around here.

The code that is not working is listed below. It does not work. It returns Null. I suck.

DECLARE @column_names varchar(8000)

SET @column_names = ''

SELECT @column_names = STUFF(@column_names,LEN(@column_names),0,C.COLUMN_ NAME + ', ')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = 'MyTable'

SELECT @column_names

little help?

View 6 Replies


ADVERTISEMENT

How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?

Apr 7, 2008

Dear All

I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.

Here is my query sample for yours to understand.



SET NOCOUNT ON

DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)

Set @qstring = 'select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm'

PRINT '-------- ITEM Products Report --------'

DECLARE ITEM_cursor CURSOR FOR

execute (@qstring)

OPEN ITEM_cursor

FETCH NEXT FROM ITEM_cursor

INTO @ITEMCODE

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ' '

SELECT @message = '----- Products From ITEM: ' +

@ITEMNAME

PRINT @message

-- Get the next ITEM.

FETCH NEXT FROM ITEM_cursor

INTO @ITEMcode

END

CLOSE ITEM_cursor

DEALLOCATE ITEM_cursor


Why i use @qstring? It is because the query will be changed by different critiera.

Regards
Edmund

View 6 Replies View Related

Possible To Return A String Of Table Values?

Dec 30, 2003

This is a portion of my stored procedure in T-SQL

CREATE PROCEDURE GetPoll
(
@PollID INT,
@PollQuestion VARCHAR(1000) Output,
@PollOptions VARCHAR(1000) Output,
@PollPercentages DECIMAL Output
)

AS

SELECT @PollQuestion = (SELECT PollQuestion FROM Polls WHERE PollID = @PollID)

SELECT @PollOptions = (SELECT [Option] FROM PollOPtions WHERE PollID = @PollID)


*SELECT @PollPercentages = (SELECT [Option], COUNT([Option]) As Num_Votes FROM Votes WHERE PollID = 1 GROUP BY [Option])


The final part(*) of this SQL will return more than one value, so is it possible for me to return all that information in one varaible with one SELECT query?? Or would I need to use a loop and query a value at a time, and store it into a delimited string?

thx in advance...

View 3 Replies View Related

Choosing Between Two Column Values To Return As Single Column Value

Sep 14, 2007

I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid       user2_guidI am trying to write a query to return a single list of all a users' friends in a single column.  Depending on who initiates the friendship, a users' guid value can be in either of the two columns.  Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way...  Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))  

View 1 Replies View Related

Integration Services :: SSIS Reads Nvarchar Values As Null When Excel Column Includes Decimal And String Values

Dec 9, 2013

I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?

CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]

View 5 Replies View Related

SQL CE 3.1 Connection String And Some Other Basic Stuff

Oct 9, 2007

Since I couldnt find an easy way to connect Orcas Beta 2 with SQL CE 3.1

I'll try by a connection string

since im totally new at this
does anyone have any idea how to get started?

Thxs!

View 4 Replies View Related

Return Multiple Values In 1 Row Sep Column

Mar 20, 2014

I'm working on a query that is asking to return data on dependents which a person can have 0-many, in a single row but sep columns. The dependent data I need to include are Dep First Name, Dep Last Name, Dep Relationship.

So my result should look something like this:

EEID| DepFirstName| DepLastName| DepRelationship| DepFirstName| DepLastName| DepRelationship
121 Billy Larson Spouse Alison Larson Child

How do I do this with SQL?

View 1 Replies View Related

Use Stuff To Define Range Of Values?

Nov 13, 2014

I have this query that calculates the next possible shipping day, based on 3 conditions:

- It has to be a workingday (WORKTIMECONTROL: 1 workingday, 0 holiday) - marked green
- There might be extra days (@xdays) required by the process - marked blue
- Customer wants their goods to be shipped on special days - marked red:select TOP 1 Transdate
from WORKCALENDARDATE
where Transdate > @startday and WORKTIMECONTROL = 1 and DATEPART(WEEKDAY,TRANSDATE)-1 in (2,4) and

[code]...

So customer 123456 accepts shipping of goods only on tuesday and thursday as in the above example "... in (2,4)". Multiple shipping days means that the Subquery returns more than one record, which gives me a headache as I don't see how to integrate this portion into my query. I tried to use the stuff function as I formally need a result that can be provided that way; but the format is incorrect as it in varchar, while an array of integer is needed.

DATEPART(WEEKDAY,TRANSDATE)-1 in (select stuff((select ',' + CAST(DELIVERYDAY as nvarchar) from CollectiveShipment where custaccount = '123456'
for xml path('')),1,1,''))

View 2 Replies View Related

Return Only Numeric Characters From A String Column

Dec 4, 2007

I have written this query to return only numeric characters from a string.

select *
from TableA
where isNumeric(Column_A) = 1

But I have discovered that the following

SELECT IsNumeric('-')
SELECT IsNumeric('£')
SELECT IsNumeric('$')
SELECT IsNumeric('+')

all return a value of 1. I do not want these in my result set.

If I use Column_A NOT LIKE '%[a-z]%' and Column_A <> '' I get characters such as "", ----- etc in my result set.

I would like a simple way of only returning numbers in my resultset.

Thanks for looking at this.

View 2 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

Return Column Names Where Values Were Not Found [stored Procedure]

Sep 5, 2004

I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.

CREATE PROC sp_test
@id INT,
@username VARCHAR(50),
@prod_id INT
AS

SELECT name FROM Customers WHERE id=@id
SELECT street FROM Addresses WHERE customer=@username
SELECT productname FROM Products WHERE id=@prod_id


It is kind of check, which has to find out if users have inserted all the necessary values or not.
Thanks for any advice.

View 4 Replies View Related

Problem Concatenating Column Values Into String...

Apr 5, 2006

I have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!

View 6 Replies View Related

Is There Any Solution For Dispalying String And Percentage Values In Single Column

Dec 1, 2007

i have a report with contains preview of percentage columns example of percentage of student marks in perticular subject like 95%. and if suppose any student not attend any test i have to dispaly like not attended statement.



so i have display two fields like 95% and not attended statement in same column, i given Cstr(Fields!Data.Value), it gives two fields with contains not attended statement of perticular query and it dispalys 0.95 % . but i need 95% and not attended statement for perticular query in same column.


is there any solution for dispalying string and percentage values in single column for given perticular query and those two values are disply same result compare with preview at the time of export to excel sheet



plese send solutions ASAP

Thanks

James vs

View 1 Replies View Related

Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

                    red     blue   green
    pen            2       0      1
    marker       0       1      0
    pencil          1       3      0
    highlighter  1       1      1 

We tried Sum but not able to display in single row.

View 2 Replies View Related

Integration Services :: SSIS Package - Replacing Null Values In One Column With Values From Another Column

Sep 3, 2015

I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.

What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column. 

I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far. 

View 3 Replies View Related

Can You Return A Cursor From A Proc?

Apr 25, 2002

Can I do something like


CREATE PROCEDURE ProcA
AS

DECLARE @temp CURSOR
BEGIN
EXECUTE ProcB @temp OUT

END

GO

I am sure that this code is not right. But, if someone can tell me a better way to solve this, I would apprciate it.

AB

View 1 Replies View Related

Transact SQL :: Search And Return String After Searched String

Sep 1, 2015

Is there way to search for the particular string and return the string after that searched string

SalesID
Rejection reason
21812

[code]....

The timeout period elapsed hence disqualified

View 3 Replies View Related

Add Symbol To Column Values And Convert Column Values To Western Number System

Feb 12, 2014

I want to add $ symbol to column values and convert the column values to western number system

Column values
Dollar
4255
25454
467834

Expected Output:
$ 4,255
$ 25,454
$ 467,834

My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application

COST is the int datatype and needs to be changed.

View 2 Replies View Related

Find In String And Return Part Of String

Mar 21, 2007

I am trying to find a way to find a certian character in a string and then select everything after that character.

for example i would look for the position of the underscore and then need to return everthing after it so in this case

yes_no

i would return no

View 7 Replies View Related

How To Return Multiple Results In 1 Fetch Cursor- Urgent Help!

Oct 7, 2004

I want to send 1 email with all clientname records which the cursor gets for me.
My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help.
I ano table to understand here about the while if right.
thanks.
+++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE test1
AS

declare @clientName varchar(1000)


declare myCursor CURSOR STATIC
for


select client_name
from clients
-------------------------
-- now prepare and send out the e-mails
declare @ToEmail varchar(255)
declare @FromEmail varchar(255)
declare @Subject varchar(255)
declare @Body varchar(2000)
declare @UserID numeric(38)
declare @UserName varchar(255)
declare @SMTPServer varchar(100)
set @SMTPServer = 'test.testserver.com'

-- loop for each record

open myCursor
fetch next from myCursor
into @clientName

--loop now:
while (@@fetch_status=0)

begin -- while(@@fetch_status=0)
-- check if valid "To" e-mail address was found
if ((@clientName is null) or (ltrim(@clientName) = ''))

begin
--should not come here anytime ideally
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = 'was emailed to wrong person'
set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'

end --if

else

begin
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = '-testing'
set @Body =

'this will send
ClientName:'+ @clientName
end --end else

-- send the e-mail
--exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
--fetch next from myCursor into @clientName

fetch next from myCursor
into @clientName

end --while(@@fetch_status=0)
exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
close myCursor
deallocate myCursor


GO

View 1 Replies View Related

Transact SQL :: Convert Comma Separated String Values Into Integer Values

Jul 28, 2015

I have a string variable

string str1="1,2,3,4,5";

I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :

declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)

But this query throws following error message:

Conversion failed when converting the varchar value '3,4,6,7' to data type int.

View 4 Replies View Related

SQL Server 2012 :: Replace All Values In String With Values From Look Up Table

Mar 19, 2014

I have a table that lists math Calculations with "User Friendly Names" that look like the following:

([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])

I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)

The new formulas need to look like the following:

([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])

I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.

How can I accomplish this?

Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.

DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )

[Code] .....

View 3 Replies View Related

T-SQL (SS2K8) :: Concatenating String Without Cursor

Mar 27, 2015

Each patient has multiple diagnoses. Is it possible to concatinate all of them in one without using a cursor?

I attach a small sample - just 3 patient (identified by VisitGUID) with the list on the left, the desired result on the right.

View 8 Replies View Related

Declare Dynamic Cursor From String

Apr 27, 2006

Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi

View 5 Replies View Related

How To Loop A Cursor And Accumulate A String Value ?

Dec 12, 2007

Hi
Why can't I loop a cursor and add values to a string in Sql server ?



Code Block
ALTER FUNCTION [dbo].[fn_Get_Project_String]
()
RETURNS nvarchar(255)
AS
BEGIN
DECLARE @Prosjekt nvarchar(255), @Pro nvarchar(255)

DECLARE c1 CURSOR
FOR select nvarchar3
FROM dbo.AllUserDataCopy

OPEN c1
FETCH NEXT FROM c1 INTO @Pro
WHILE @@FETCH_STATUS = 0

BEGIN
select @Prosjekt = '<item>' + @Pro + '</item>' + ''
FETCH NEXT FROM c1 INTO @Pro
select @Prosjekt = @Prosjekt + @Pro
END

CLOSE c1
DEALLOCATE c1

RETURN @Prosjekt
END





Ivar


View 4 Replies View Related

How Do I Concatenate Different Values For The Same Primary Key Without Using Cursor..

Mar 21, 2002

Can I avoid cursor (use while of if statement instead ) if for example I have two sales people for the same company and I need one row per company?

View 1 Replies View Related

Transact SQL :: Declaring Cursor Causing Select Statements Included Within A Function Cannot Return Data To Client?

Sep 29, 2015

I cannot find the problem with this function.

ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;

[code]....

I am getting the following error on line 75:

Select statements included within a function cannot return data to a client.

This is happening when declaring TrackUpdateCursor

The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?

View 20 Replies View Related

Save Me From A CURSOR. Flag Values That Won't Convert.

Dec 10, 2007

Hey all:

Right now I have a cursor that makes me want to puke. This is the last cursor in my current project and I want to replace it with a much faster set based operation.

Here is the problem. I have a table with say 1-3 million records. There are fields that get loaded in with date information. These fields are varchar because the date information could very well be mangled data that needs to be reviewed by a user. What I need is to go through these varchar fields and flag the values that cannot convert to smalldatetime.

I have another table that houses the primary key and the field of the record that cannot convert.

Essentially, I have a series of filters that run and flag using set based stored procedures. If there is a record that gets through that contains a value that cannot be converted, I have a cursor that steps through the data and attempts to convert the value. If it is able to be converted, then it continues on until it finds the value that is holding up the conversion.

I guess if I can run a query that will return all records that can convert for the field (or can't convert) I'd be all set. Any help here is appreciated.

--Thanks--

View 3 Replies View Related

‘default To Local Cursor’ Causes Errors In String-built Declares

Sep 20, 2000

Hi,

I would like to use the dboption ‘default to local cursor’ to true.

But when I set it to true for my database, cursors that are built in a ‘string’ and then executed return an error :

Select @SQL = 'Declare SICCursor cursor For Select SIC1 From ' + @StateTable + ' Where BusinessName = ' + '''' + @BusinessName + '''' + ' Order By PubDate desc'

Exec(@SQL)

Open SICCursor

Openning the cursor returns the error message

“A cursor with the name 'SICCursor' does not exist.”

What am I doing wrong (cursors declared ‘normally’ do not have a problem)?

Thanks,
Judith

View 1 Replies View Related

Return Dat In String

Jul 25, 2007

hi! how would i return the current day in string format?

the output should be sun,mon,tue,wed,thu.. etc?

pls. help.. thanks

View 3 Replies View Related

Return Into One String

May 5, 2006

I have a table with a column called AccessTypes which contains a singleletter.I want to return these accesstypes from a query into one string. e.g.if there were 3 entries of A, S and DI want to select them and instead of returning 3 rows, I want just 1string like "ASD"can it be done?

View 3 Replies View Related

CURSOR - Conversion Failed When Converting Date And / Or Time From Character String

May 15, 2015

This is my code and I don't know why this error keeps coming out : PS : I did cursor to execute query.Th error showed is bold:

DECLARE RegCreatedDate CURSOR FOR
SELECT DISTINCT (CONVERT(NVARCHAR,CreatedDate,103)) 
FROM CA_Registration WHERE Month(CreatedDate)= @paMonthIn AND YEAR(CreatedDate)=@paYearIn
OPEN RegCreatedDate
FETCH NEXT FROM RegCreatedDate INTO @RegCreatedDate
WHILE @@FETCH_STATUS = 0

[Code] ....

View 9 Replies View Related

SQL Return Values In ASP

Feb 14, 2001

How do you get the return value(in ASP) of @@identity in a stored procedure while inserting a row?

View 2 Replies View Related







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