Having problems UPDATING with a stored procedure with a variable

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB, but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB , but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the each latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.|||

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far. I am working in SQL server 2005.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB , but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.|||

You are returning the results of a data manipulation - there may not be a 'match' between the resultset and the actual table.

My 'simple' recommendation is to:

capture the resultset in a @.Table variable, then use the [ID] value from that table variable to UPDATE the data, finally, returning the contents of the table variable with a SELECT.|||

Thanks for the reply. I posted this question a few too many times (was told it was deleted by administrator!).

Anyway, [ID] is not the primary key, [acmdtn_RECID] is. Any changes made to the master database simply add another record, and retain the old record, so historical queries can be run. So there can be dozens of records for each [ID]. What I want is the most recent record, for each [ID], as of a certain date.

I guess I just don't get why I can display the information, but I can't write back to the database based on that displayed information. It's a complex query, but there's no joins, or other tables involved, there can only be a 1:1 relationship between records in the table, and records in the resultset.

Have:DateTime, Need: string, use in EXEC sp_executesql parm list

Hello,
Below is the extract of code I am stuck on. My goal is to take the DateTime
value in the variable @.End and convert it for creating the sp_executesql
params.
The problem:
======================
declare @.End datetime
declare @.E varchar
select @.End=comm_end from CommPeriod
where period_id = (select min(period_id) from CommPeriod
where period_closed = 0 and CountryCode='US')
and CountryCode = 'US'
print @.End
SET @.E = CONVERT(VARCHAR, @.End , 112)
print @.E
The Output:
==================
Dec 31 2004 11:59PM
2
I humbly ask for a solution, clues, tips and...
Thanks,
Bob
> declare @.E varchar
No length specified, implies varchar(1). Make it varchar(8).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob B via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:dbba533561de44c18d098a9ec4fb6472@.droptable.co m...
> Hello,
> Below is the extract of code I am stuck on. My goal is to take the DateTime
> value in the variable @.End and convert it for creating the sp_executesql
> params.
> The problem:
> ======================
> declare @.End datetime
> declare @.E varchar
> select @.End=comm_end from CommPeriod
> where period_id = (select min(period_id) from CommPeriod
> where period_closed = 0 and CountryCode='US')
> and CountryCode = 'US'
> print @.End
> SET @.E = CONVERT(VARCHAR, @.End , 112)
> print @.E
> The Output:
> ==================
> Dec 31 2004 11:59PM
> 2
>
> I humbly ask for a solution, clues, tips and...
> Thanks,
> Bob
sql

Have:DateTime, Need: string, use in EXEC sp_executesql parm list

Hello,
Below is the extract of code I am stuck on. My goal is to take the DateTime
value in the variable @.End and convert it for creating the sp_executesql
params.
The problem:
======================
declare @.End datetime
declare @.E varchar
select @.End=comm_end from CommPeriod
where period_id = (select min(period_id) from CommPeriod
where period_closed = 0 and CountryCode='US')
and CountryCode = 'US'
print @.End
SET @.E = CONVERT(VARCHAR, @.End , 112)
print @.E
The Output:
==================
Dec 31 2004 11:59PM
2
I humbly ask for a solution, clues, tips and...
Thanks,
Bob> declare @.E varchar
No length specified, implies varchar(1). Make it varchar(8).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob B via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:dbba533561de44c18d098a9ec4fb6472@.SQ
droptable.com...
> Hello,
> Below is the extract of code I am stuck on. My goal is to take the DateTim
e
> value in the variable @.End and convert it for creating the sp_executesql
> params.
> The problem:
> ======================
> declare @.End datetime
> declare @.E varchar
> select @.End=comm_end from CommPeriod
> where period_id = (select min(period_id) from CommPeriod
> where period_closed = 0 and CountryCode='US')
> and CountryCode = 'US'
> print @.End
> SET @.E = CONVERT(VARCHAR, @.End , 112)
> print @.E
> The Output:
> ==================
> Dec 31 2004 11:59PM
> 2
>
> I humbly ask for a solution, clues, tips and...
> Thanks,
> Bob

Handling variables

I have a script task that has a ReadWriteVariable = FilePath. I am doing the following below to calculate the FilePath variable. Instead of the output being C:\Rapagator\Downloaded\RETS_Search_ResidentialProperty_20060403094343.txt, it just shows RETS_Search_ResidentialProperty_20060403094343.txt. Any ideas how to resolve this problem?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Public GetFilePath As String = ""

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
GetFilePath = "C:\Rapagator\Downloaded" + Row.FileName
End Sub
Public Overrides Sub PostExecute()
Variables.FilePath = GetFilePath
MyBase.PostExecute()
End Sub

End Class

How do you know the variable value is only the filename part you state? You cannot debug inside the Script Component, so perhaps add a MessageBox or log an event to write out the values you have just to be sure. Perhaps set a breakpoint as well and add the variable to the watch window. You do know that the Variables window only shows design-time values during runtime, even when completed or stopped on a breakpoint. Use the watch window to get real values. This does seem rather strange, are you only expecting one row to flow through this component; otherwise you will only store the last row's value in the variable. Is that what you want?

Does the filename start with a "\" ? If not the path looks invalid - C:\Rapagator\DownloadedMYFILE. Top tip, use System.IO.Path.Combine(x, y) as that will sort out path qualifiers for you.

Handeling NULL variable

Good afternoon,

I'd like to like how you guys handle a situation like the one I'm going to describe in just a moment. I'm pretty sure I'm not handeling it in the best way, so I'll just try to explain the situation and learn something from the gurus.

Let's imagin I have the following tables:

[Companies]Id [Uniqueidentifier] [PK]Alias [NVarChar]Status [Bit][Campanies_JobTitles]Id [PK]CompanyId [Uniqueidentifier]Alias [NVarChar]Status [Bit]

If I want to know the job titles of company 1 I could easely set the following stored procedure:

SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1AND JT.CompanyId = @.CompanyIdORDER BY JT.AliasASC;

Now, if the user wants to list the job titles across all the companies I could pass a special value and identify that, as follows:

IF (@.CompanyIdISNOT NULL)BEGIN SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1AND JT.CompanyId = @.CompanyIdORDER BY JT.AliasASC;ENDELSE BEGIN SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1ORDER BY JT.AliasASC;END

This works fine. I just find it a bit counter productive to copy paste the query and wrap it into an if statement. So, the bottom line is: how can I do this in a more "professional" way?

Best regards.

Hi,

What I believe, the query is good enough because at certain point you have to check if the company Id exist or not so that if exist you can search it by the id, if not then show all.

Thanks and best regards,

|||

farazsk11:

Hi,

What I believe, the query is good enough because at certain point you have to check if the company Id exist or not so that if exist you can search it by the id, if not then show all.

Thanks and best regards,

I understand what you are saying, although that check was not on the posted code for the sake of simplicity. But, that wouldn't answer the underlying question that is: how can I "reuse" the query so I don't have to repeat it thoughout the procedure? Is there a function that can solve this like ISNULL allows you to handle the values that return as null.

|||

What about this:

SELECTJT.IdAS Id,JT.AliasAS AliasFROM Companies_JobTitles JT
WHERE JT.Status = 1
AND JT.CompanyId =ISNULL(@.CompanyId,JT.CompanyId)ORDER BY JT.Alias

|||

limno:

What about this:

SELECTJT.IdAS Id,JT.AliasAS AliasFROM Companies_JobTitles JT
WHERE JT.Status = 1
AND JT.CompanyId =ISNULL(@.CompanyId,JT.CompanyId)ORDER BY JT.Alias

That was exactly what I was looking for. I didn't know ISNULL could be used in that context.

Thanks a lot limno :)