Showing posts with label figure. Show all posts
Showing posts with label figure. Show all posts

Monday, March 26, 2012

How to use UPDATETEXT within the context of inserting a new row

All -

I'm trying to insert a row into a table. One of the columns in this table is a text field. I can't figure out how to use UPDATETEXT to insert data into this text field for a brand new row. The examples I see all require a text pointer to the column. However - I haven't even inserted the row - so how would I create a text pointer?

Here's the example from Books Online -

DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(pr_info) FROM pub_info pr, publishers p WHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @.ptrval 88 1 'b' GO

How would I create the text pointer (TEXTPTR) for a record that hasn't even been inserted yet?

Incidentally - my issues would be solved if the data for this text column was always less than 8K (then I could use varchar(8000) - but that isn't always the case.

Help!

I'm assuming that you're running SS2000.

UPDATETEXT operates on tables.

If the data you want to manipulate isn't in a table, where is it?

|||

Note that UPDATETEXT is going away in a future version of SQL Server. Use varchar(max) if this is a 2005 database and then use the .WRITE construct in the UPDATE statement.

Well, you can just use a text parameter to a stored procedure and just pass in all of the text... That is the simplest answer. You will have to create the row first, and create the text pointer:

drop table test
go
create table test
(
testId int,
value text
)
go
insert into test(testId)
values (1)
go

Update test
set value = 'foo'
where testId = 1
and value is null

DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(value)
FROM test
WHERE testId = 1


UPDATETEXT test.value @.ptrval null null 'b'
GO

select *
from test

Returns:

testId value

--

1 foob

|||Thanks so much - this seemed to work. And yes I am stuck with SQL Server 2000 right now. It seems to me so clunky to update text fields this way - so I'll be real happy once I migrate to 2005.

Thanks again for the help.

Monday, March 12, 2012

How to use multle values for Where clause

Hi, I have a unique problem that I am currently unable to figure out. I need to populate a where clause in a SQL statement that has multiple values, however those values always change because they are in another table. The end result that I want to end up with is a list of subs that belong to all of the UCI's that were selected for a particular bid number.

I have the following tables

tblBid with two columns. Bid_ID, and Uci_ID . This table contains multlple rows with the same Bid_ID but the Uci_ID is never the same for the current Bid_id. For example. If I had a Bid_ID of 123, I might have mutliple records listing

bid_id Uci_id

123 1000

123 2000

123 1050

tblSubs_By_Uci that has two columns. Sub_ID, and Uci_ID . This talbe contains a list of Uci_id's that Subs belong to. So I will have only multiple Sub_id and mulitple UCI_ID's because a sub can belong to mulitple Uci_ID's.

Uci_ID Sub_ID

1000 456

1000 2345

2000 456

1050 2345

2000 2345

This is the statement I am using to return the Uci's from the Bid table with bid_id of 123. For example. when I run the following sql statement, it will list all of the UCI's for bid_id 123. SELECT Uci_ID from tblBid where Bid_ID = 123 . That produces a list of UCI's. Now I want to find each sub that belongs to each of the UCI's using that list.

SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID = (SELECT Uci_ID from tblBid WHERE Bid_ID = 123) . I of course get an error from sql saying that I can not pass multiple values to the Where clause.

Can someone please help point me in the right direction. I have been searching on the net for days trying to figure this out. I am open to any suggestions.

This is how you have to do it

SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID IN (SELECT Uci_ID from tblBid WHERE Bid_ID = 123)

|||Try this... SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID in (SELECT Uci_ID from tblBid WHERE Bid_ID = 123)|||

There are 2 ways.

SELECT Sub_IDfrom tblSubs_BY_UCIwhere Uci_IDin (SELECT Uci_IDfrom tblBidWHERE Bid_ID = 123 )-- or ----SELECT tb.Uci_ID , ts.Sub_IDfrom tblSubs_BY_UCI ts , tblBid tbwhere ts.Uci_ID = tb.Uci_IDand tb.Bid_ID = 123
Hope this will help.|||

Thanks.

By Changing the = to IN, it worked perfectly.

Sunday, February 19, 2012

How to use a Table Variable name in a Stored Proc?

Hi All,
I'm trying to figure out how to use a variable within a stored proc to be
used as the table name.
I am passing in the name of a table to a stored proc and getting the table
name form sysobjects. This works fine; but when I go to use that name in a
query, inside the same Stored Procedure, it fails.
Example:
CREATE PROCEDURE dbo.Select_Data
@.tbl VarChar(30)
AS
SELECT T = sysobjects.name
FROM sysobjects
WHERE sysobjects.name = @.tbl
SET NOCOUNT ON
/*This Part Fails*/
SELECT *
FROM T
/*This Part Fails*/
SET NOCOUNT OFF
GO
Any help on this would be appreciated.
Thanks,
John.sp_executesql
Es.
DECLARE @.Sqlstat nvarchar(50)
set @.Sqlstat='SELECT * FROM ' + @.Tbl
exec sp_executeSql @.Sqlstat
Francesco Anti
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:On9DYabRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I'm trying to figure out how to use a variable within a stored proc to be
> used as the table name.
> I am passing in the name of a table to a stored proc and getting the table
> name form sysobjects. This works fine; but when I go to use that name in
> a query, inside the same Stored Procedure, it fails.
> Example:
> CREATE PROCEDURE dbo.Select_Data
> @.tbl VarChar(30)
> AS
> SELECT T = sysobjects.name
> FROM sysobjects
> WHERE sysobjects.name = @.tbl
> SET NOCOUNT ON
> /*This Part Fails*/
> SELECT *
> FROM T
> /*This Part Fails*/
> SET NOCOUNT OFF
> GO
> Any help on this would be appreciated.
> Thanks,
> John.
>|||http://www.sommarskog.se/dynamic_sql.html#Dyn_table
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:On9DYabRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I'm trying to figure out how to use a variable within a stored proc to be
> used as the table name.
> I am passing in the name of a table to a stored proc and getting the table
> name form sysobjects. This works fine; but when I go to use that name in
> a query, inside the same Stored Procedure, it fails.
> Example:
> CREATE PROCEDURE dbo.Select_Data
> @.tbl VarChar(30)
> AS
> SELECT T = sysobjects.name
> FROM sysobjects
> WHERE sysobjects.name = @.tbl
> SET NOCOUNT ON
> /*This Part Fails*/
> SELECT *
> FROM T
> /*This Part Fails*/
> SET NOCOUNT OFF
> GO
> Any help on this would be appreciated.
> Thanks,
> John.
>|||Don't create generic procs like this. It's just plain bad design and
serves no useful purpose: slow; insecure; difficult to maintain in
code. The standard pattern is to create separate procs for each table
SELECT, and it should be very easy to generate those semi-automatically
from the info schema tables if you like.
David Portas
SQL Server MVP
--