AT the moment i have an SQL SELECT Statement as follows....

SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle

FROM HomePageImage H

JOIN shortlist SL on H.StoryId = SL.id

order by date DESC

is it possible to join to another table in the same query to get a value out.

it would be JOIN categories C on H.CategoryID = C.CategoryID

is this possible. can anyone help?

This should help

http://www.vb-tips.com/InnerJoin.aspx

|||

is this what you want?

SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle

FROM HomePageImage H

JOIN shortlist SL on H.StoryId = SL.id JOIN categores C on h.CategoryID=C.CategoryID

order by H.date DESC

|||

Yes, Thanks for help.

Asterisk in SQL

I'm trying to creat a search form and want to use the Like clause in my select statement so that a user can enter part of a word rather than the entire word. When I use this sql I get no results:

SELECT DISTINCT Keyword.CodeID FROM Keyword INNER JOIN Code ON Keyword.CodeID = Code.CodeID WHERE ((Keyword.Keyword)Like '*ARR*'AND (Code.ProgLang)='VB.NET') ORDER BY Keyword.CodeID

The problem is with the * . If I remove the * it works fine. If I use the code within Access rather than from my aspx code, it works fine. Is there a work around for this?

Hi,

% (percent) is the standard wildcard character. Access client does support *, but when you use it via OleDB (Jet provider) it also requires %.

Therefore put

SELECT DISTINCT Keyword.CodeID FROM Keyword INNER JOIN Code ON Keyword.CodeID = Code.CodeID WHERE ((Keyword.Keyword)Like '%ARR%'AND (Code.ProgLang)='VB.NET') ORDER BY Keyword.CodeID

|||That did it. Thanks!!!

Assistance with Stored Procedure

I currently have a sql statement that works great. I want to convert it
to a stored procedure so I can generate results from a webpage. Below
is the stored procedure that is working fine.

select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
SIDPERS_PERS_UNIT_TBL.UNAME,
SIDPERS_PERS_UNIT_TBL.ADDR_CITY, SIDPERS_PERS_UNIT_TBL.PR_NBR,
[tblPersonnel].[ADDR_CITY] + ' ' + [tblPersonnel].[ZIP] AS HOR,
SMOSC=(case [tblSTAP Info].[SMOS Considered]
when "1" then "Yes"
else "No"
end),
FIRSTSGTC =(case [tblSTAP Info].[1SG]
when "1" then "Yes"
else "No"
end),
CSMC=(case [tblSTAP Info].[CSM]
when "1" then "Yes"
else "No"
end),
tblPersonnel.*, [tblSTAP Info].*
FROM SIDPERS_PERS_UNIT_TBL
INNER JOIN (tblPersonnel INNER JOIN [tblSTAP Info] ON
tblPersonnel.SSN_SM = [tblSTAP Info].SSN)
ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__')) and
(tblPersonnel.PAY_GR = 'E5')
and (SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))

and ([tblSTAP Info].TotalPoints >=
(case tblPersonnel.PAY_GR
when "E4" then 350
when "E5" then 400
when "E6" then 450
when "E7" then 500
when "E8" then 600
else 0
end))
AND [tblSTAP Info].NotConsidered = 0
ORDER BY tblPersonnel.PAY_GR DESC , [tblSTAP Info].TotalPoints DESC ,
tblPersonnel.NAME_IND;

I would like the 3 items under the where clause to recieve a variable
from the website:

(SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__'))

(tblPersonnel.PAY_GR = 'E5')

(SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))

Everytime I try to make this a stored procedure and try to pass multiple
values in the PMOS field, I get an error stating too many variables.

If anyone can tell me what the Stored Procedure should look like AND
what the ASP should look like to pass the variables, I would be much
obliged.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it![posted and mailed, please reply in news]

Tod Thames (tod.thames@.nc.ngb.army.mil) writes:
> I currently have a sql statement that works great. I want to convert it
> to a stored procedure so I can generate results from a webpage. Below
> is the stored procedure that is working fine.
> select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
> SIDPERS_PERS_UNIT_TBL.UNAME,
> SIDPERS_PERS_UNIT_TBL.ADDR_CITY, SIDPERS_PERS_UNIT_TBL.PR_NBR,
> [tblPersonnel].[ADDR_CITY] + ' ' + [tblPersonnel].[ZIP] AS HOR,
> SMOSC=(case [tblSTAP Info].[SMOS Considered]
> when "1" then "Yes"
> else "No"
> end),
> FIRSTSGTC =(case [tblSTAP Info].[1SG]
> when "1" then "Yes"
> else "No"
> end),
> CSMC=(case [tblSTAP Info].[CSM]
> when "1" then "Yes"
> else "No"
> end),
> tblPersonnel.*, [tblSTAP Info].*
> FROM SIDPERS_PERS_UNIT_TBL
> INNER JOIN (tblPersonnel INNER JOIN [tblSTAP Info] ON
> tblPersonnel.SSN_SM = [tblSTAP Info].SSN)
> ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
> WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__')) and
> (tblPersonnel.PAY_GR = 'E5')
> and (SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))
> and ([tblSTAP Info].TotalPoints >=
> (case tblPersonnel.PAY_GR
> when "E4" then 350
> when "E5" then 400
> when "E6" then 450
> when "E7" then 500
> when "E8" then 600
> else 0
> end))
> AND [tblSTAP Info].NotConsidered = 0
> ORDER BY tblPersonnel.PAY_GR DESC , [tblSTAP Info].TotalPoints DESC ,
> tblPersonnel.NAME_IND;
> I would like the 3 items under the where clause to recieve a variable
> from the website:
> (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__'))
> (tblPersonnel.PAY_GR = 'E5')
> (SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))
>
> Everytime I try to make this a stored procedure and try to pass multiple
> values in the PMOS field, I get an error stating too many variables.
> If anyone can tell me what the Stored Procedure should look like AND
> what the ASP should look like to pass the variables, I would be much
> obliged.

The SP would look like this:

CREATE PROCEDURE TodTahems @.rpt_seq_code_pattern varchar(25),
@.pay_gr char(2),
@.pmos text
select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
...
ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
JOIN iter_charlist_to_table(@.pmos) AS pmos ON
SUBSTRING (tblPersonnel.PMOS,1,3) = pmos.str
WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE @.rpt_seq_code) and
(tblPersonnel.PAY_GR = @.paygr)
...

The function iter_charlist_to_table unpacks a comma-separated list
into a table. You find the code here:
http://www.sommarskog.se/arrays-in-...list-of-strings

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I need a little more assistance. I did a copy and paste of the
"char_to_table_sp" to create the procedure in my DB. I followed the
examples in you email.

I have everything working to push the variables from the asp page to the
stored procedure. The pages work fine when I only put in one value,
however it doesn't work when I input more than one value.

The information below is provided.

standinglist2_test 'AAA_', 'E5', '71L, 75H'

doesn't return any values.

standinglist2_test 'AAA_', 'E5', '71L'

returns several rows.

Here is the SP I created.

CREATE procedure standinglist2_test
@.rsc varchar(4),
@.paygr varchar(3),
@.mos varchar (5)
as
CREATE TABLE #strings (str nchar (20) NOT NULL)
EXEC charlist_to_table_sp @.mos

select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
SIDPERS_PERS_UNIT_TBL.UNAME,
SIDPERS_PERS_UNIT_TBL.ADDR_CITY, SIDPERS_PERS_UNIT_TBL.PR_NBR,
[tblPersonnel].[ADDR_CITY] + ' ' + [tblPersonnel].[ZIP] AS HOR,
SMOSC=(case [tblSTAP Info].[SMOS Considered]
when "1" then "Yes"
else "No"
end),
FIRSTSGTC =(case [tblSTAP Info].[1SG]
when "1" then "Yes"
else "No"
end),
CSMC=(case [tblSTAP Info].[CSM]
when "1" then "Yes"
else "No"
end),
tblPersonnel.*, [tblSTAP Info].*
FROM
#strings s INNER JOIN
SIDPERS_PERS_UNIT_TBL INNER JOIN
tblPersonnel INNER JOIN
[tblSTAP Info] ON
tblPersonnel.SSN_SM = [tblSTAP Info].SSN
ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
ON (SUBSTRING(tblPersonnel.PMOS,1,3) = s.str)
WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE (@.rsc)) and
(tblPersonnel.PAY_GR = @.paygr)
and (SUBSTRING (tblPersonnel.PMOS,1,3) IN (@.mos))

and ([tblSTAP Info].TotalPoints >=
(case tblPersonnel.PAY_GR
when "E4" then 350
when "E5" then 400
when "E6" then 450
when "E7" then 500
when "E8" then 600
else 0
end))
AND [tblSTAP Info].NotConsidered = 0
ORDER BY tblPersonnel.PAY_GR DESC , [tblSTAP Info].TotalPoints DESC ,
tblPersonnel.NAME_IND;

Your help is really appreciated. If you need any other information to
assist, please let me know.

I am unable to access the website you reference in your first response
from my office. I had to wait until i got home to try it. Must be a
firewall issue.

Thanks again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Further information below:

I am using SQL 7, so I went to the SQL Server 7 link on your site. I
used the List-of-string Procedure to try and make it work as opposed to
information below.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Tod Thames (tod.thames@.nc.ngb.army.mil) writes:
> The information below is provided.
> standinglist2_test 'AAA_', 'E5', '71L, 75H'
> doesn't return any values.

There is a very simple explanation:

> CREATE procedure standinglist2_test
> @.rsc varchar(4),
> @.paygr varchar(3),
> @.mos varchar (5) <------

Change the declaration of @.mos to varchar(8000) or to text, to avoid
truncation issues.

> I am unable to access the website you reference in your first response
> from my office. I had to wait until i got home to try it. Must be a
> firewall issue.

I registered the domain in the beginning of December, so it could be
slow propagation somewhere. You could also try with
http://www.algonet.se/~sommar, which is the same site, but a less
pretty URL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I tried changing this:

> @.mos varchar (5) <------

to

@.mos varchar (8000)

I had the same problem. When one variable is sent, it works fine, but
when several are sent, it returns no rows.

So, I tried changing it to:

@.mos text

and received this error:

Server: Msg 8114, Level 16, State 1, Line 1
Error converting data type text to ntext.
Server: Msg 306, Level 16, State 1, Procedure standinglist2_test, Line 9
The text, ntext, and image data types cannot be used in the WHERE,
HAVING, or ON clause, except with the LIKE or IS NULL predicates.

I think I am very close to getting this resolved. Does anyone else have
any ideas?

I tried the link you provided in your last post and still couldn't get
to the site. I think it must be the firewall here.

Tod Thames

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Tod Thames (anonymous@.devdex.com) writes:
> I had the same problem. When one variable is sent, it works fine, but
> when several are sent, it returns no rows.

I went back to the stored procedure, and there are more problems:

and (SUBSTRING (tblPersonnel.PMOS,1,3) IN (@.mos))

You need to remove this condition.

If there are further problems, I would recommend that you do some
debugging on your own. First thing is to add a "SELECT * FROM #strings"
to see that the table is correct. Next is to remove condition, until
rows starts to pop up. That's probably a more effective way than asking
for help and wait for someone to come by in the newsgroups.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks so much for the assistance. It worked after I took that last
statement out of the SP. I actually tried some debugging, but I am not
very proficient at it. I did the "select * from #strings", but received
this message.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#stings'.

I couldn't figure out how to get the results from a temporary table.
Since I couldn't get the results from the table that is populated, I
didn't really know where to go from there.

Anyway, it is working now and I thank you very much. That sp you wrote
amazes me.

Tod Thames

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Tod Thames (anonymous@.devdex.com) writes:
> Thanks so much for the assistance. It worked after I took that last
> statement out of the SP. I actually tried some debugging, but I am not
> very proficient at it. I did the "select * from #strings", but received
> this message.
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#stings'.

Judging from the error message, you mispelled the table name. But that
may of course been a type when you posted.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Assistance on SUM() statement

I have a table #changes with two columns: familyID, Versiontime

For each record in the #changes table I need to compute the income from another table FamilyIncome with colunms: familyID, IncomeType, EffectiveDate, Amt. Example Below:

FamID, IncType, EffDate, Amt
100, 10, 01/01/2003, $50.00
100, 20, 01/01/2003, $50.00
100, 30, 01/01/2003, $50.00
100, 10, 02/02/3003, $100.00
100, 20, 02/02/3003, $100.00
100, 30, 02/02/3003, $100.00
100, 40, 02/02/3003, $100.00
100, 20, 03/03/3003, $75.00
100, 30, 03/03/3003, $75.00
100, 40, 03/03/3003, $75.00

So if I'm looking for the Incomes on the following dates (which are in the #changes table) it should be:

01/02/2003 - $150.00 (The three records effective on 01/01/2003)
02/10/2003 - $400.00 (The four records effective on 02/02/2003)
04/01/2003 - $325.00 (One record from 02/02/2003 is still effective (Type 10) plus the three records effective on 03/03/2003)

Any help is greatly appreciated,

BrentCan you post the DDL?

But it seems like a join between the two and a GROUP by with a SUM

something like

SELECT EFF_DATE, SUM(AMT)
FROM myTable1 a myTable2 b
ON a.key = b.key
GROUP BY EFF_DATE|||Brett,
I have tried a couple variations on this theme and so far they do not deliver the desired results:

1. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid and EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
from #changes
group by familyid, cast(versiontime as datetime),#changes.versiontime
order by familyid, versiontime desc
THIS CODE WORKS FOR THE FIRST DATE AND RETURNS THE $150.00 DESIRED, BUT ON ANY FUTURE DATES IT ADDS THE NEW INCOME AND KEEPS A RUNNING TOTAL (I.E. $550.00 INSTEAD OF $400.00)

2. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid
HAVING EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
from #changes
group by familyid, cast(versiontime as datetime),#changes.versiontime
order by familyid, versiontime desc
THIS CODE ONLY RETURNS A RESULT SET FOR THE LAST DATE IN THE SEQUENCE AND THEN IT RETURNS $225.00 (ONLY THE RECORDS WITH A 03/03/2003) DATE)
At this time I'm just trying to isolate the effdate calculations. Also whoever reads this all the dates should be 2003 the 3003 for the year is a typo.

Thanks,

Brent
Originally posted by Brett Kaiser
Can you post the DDL?

But it seems like a join between the two and a GROUP by with a SUM

something like

SELECT EFF_DATE, SUM(AMT)
FROM myTable1 a myTable2 b
ON a.key = b.key
GROUP BY EFF_DATE|||I don't understand your results...where do these dates come from:

01/02/2003 - $150.00 (The three records effective on 01/01/2003)
02/10/2003 - $400.00 (The four records effective on 02/02/2003)
04/01/2003 - $325.00 (One record from 02/02/2003

They don't exists in your data...|||Originally posted by Brett Kaiser
I don't understand your results...where do these dates come from:

They don't exists in your data...

Those dates come out of the #changes table which I only included the fields not an example. What I have is 43,000 records in the #changes table that are familyID's and Dates on which I need to perform several calculations (size of the family, income, fee schedule, etc) right now I'm hung up on getting the income which I need in order to determine the fee (fee is based on family size and income)

Brent

assigning Select results to local vars in SP

Hi. I'd like to assign the results of a select statement to a local
variables in my stored procedure. My intent is something like this:
SELECT TOP 1 field1,field2,field3 FROM table WHERE field1 = @.InParam
only, some how I'd like to get the field2,field3 into variables. Can
this be done?
Thanks in advanceFirst of all, do not use TOP without using ORDER BY, unless selecting
somewhat random results is required (which I gues is not).
Other than that, this is the way to go:
select @.variable_name = owner.table.colum
from owner.table
where (owner.table.another_column = @.parameter)
Don't forget to look up using local variables in Books Online.
ML|||Johnny,
Something like this:
USE Pubs
GO
CREATE PROC TESTPROC
@.AID varchar(11)
AS
DECLARE @.FName varchar(30)
DECLARE @.LName varchar(30)
SELECT @.FName = au_fname, @.LName = au_lname
FROM authors
WHERE au_id = @.AID
PRINT @.FName + ' ' + @.LName
GO
EXEC TESTPROC '172-32-1176'
HTH
Jerry
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1127950731.353336.297780@.g43g2000cwa.googlegroups.com...
> Hi. I'd like to assign the results of a select statement to a local
> variables in my stored procedure. My intent is something like this:
> SELECT TOP 1 field1,field2,field3 FROM table WHERE field1 = @.InParam
> only, some how I'd like to get the field2,field3 into variables. Can
> this be done?
> Thanks in advance
>|||Thanks Jerry, I'll try this out!|||Just be careful! If the SELECT returns more than 1 rows, you will *not* get
an error. The
variable(s) will contain the value for an unspecified row.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1127953579.251983.177820@.o13g2000cwo.googlegroups.com...
> Thanks Jerry, I'll try this out!
>

Assigning multiple values to a parameter in stored procedure

Hi:
How do you write a SQL statement in a stored procedure so
that it will allow you to assign multiple values to a
parameter?
For instance in this example below, depending on what the
users select on the front-end application, the values
assign can be one customer id value or multiple customer
id values:
Create procedure dbo.SP_Test
As @.CustID varchar(3)
Select * from tblCust where customerid = @.CustID
Please help!This is probably not the best solution but it should work
create procedure sp_test
@.cust_id varchar(50)
as
set nocount on
exec ('select * from tblcust where customerid in (' + @.cust_id +')')
go
This procedure would be called as sp_test '50' for one cust_id or sp_test
'50, 55, 100' for several cust_id's

assign variable within SP

I use SQL Server 2005 and in a Stored Procedure I want to execute a sql statement and assign the result to a variable. How can I do that?
The name of the column I want to retreive the value from is "UserID"
Here's my SP so far:

ALTERPROCEDURE [dbo].[spUnregisterUser]

@.UserCodeint

AS

BEGIN

SETNOCOUNTON;declare @.uiduniqueidentifier--get useridSELECT @.uid=UserIDFROM tblUserDataWHERE UserCode=@.UserCode-- Delete userUPDATE tblUserDataSET IsDeleted='True'WHERE UserCode=@.UserCode

END

That's pretty much how you'd do it. What's not working?

Assign Variable for FileName of Source File

I am wanting to capture the file name I am using to load data from and use it in a SQL statement to insert it along with the data that I load.

I am using a ForEach container to load all my .txt files but cannot figure out how to capture the name of each source file as it loops through the files and then add it to my insert statement that is populating my history table. I would think that the ForEach container has that information, but I do not see how to access it and assign it to a variable that I could use in my SQL statement.

Any ideas or suggestions?

The ForEach loop container can store the name of eac enumerated file into a variable. This article explains how to do that: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

Once its in a variable you can do pretty much anything you want with it and it seems you already know how to do that. If not, just reply here.

-Jamie

|||

Thanks for the link, but I have was able to setup a variable @.[User::FN] to the ConnectionString property associated with the flat file connection, and the looping and loading effort is working fine.

This may seem like a stupid question, and I apologize, but I cannot figure out how to reference that variable and use it in my execute SQL task that follows later and does my insert of the data into my table. What I want to do is something like this:

Insert into Table1 Select a, b, c, @.FN From Table2

The parser requires that I declare the variable and does not seem to recognize the @.[User::FN] variable.

Any suggestions?

|||

Yes, use an expression. This (sort of) shows you how:

http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

Assign T-SQL variables in dynamic SQL statement?

I have a dynamic SQL statement in which I need to assign values to
variables.
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC(@.querystring)
This doesn't work as it doesn't assign values to @.rows and @.pages that can
be accessed within the stored procedure.
Any suggestions?
Use sp_executesql with output parameters:
DECLARE @.rows INT
DECLARE @.pages INT
DECLARE @.querystring NVARCHAR(300)
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC sp_executesql
@.querystring,
N'@.rows INT OUTPUT, @.pages INT output, @.perpage INT',
@.rows OUTPUT, @.pages OUTPUT, @.perpage
PRINT @.pages
PRINT @.rows
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Joe" <joe@.hotmail.com> wrote in message
news:eNR78M7uFHA.3256@.TK2MSFTNGP09.phx.gbl...
> I have a dynamic SQL statement in which I need to assign values to
> variables.
> SELECT @.querystring = 'SELECT @.rows = COUNT(*)
> @.pages = COUNT(*) / @.perpage
> FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
> EXEC(@.querystring)
> This doesn't work as it doesn't assign values to @.rows and @.pages that can
> be accessed within the stored procedure.
> Any suggestions?
>

Assign T-SQL variables in dynamic SQL statement?

I have a dynamic SQL statement in which I need to assign values to
variables.
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC(@.querystring)
This doesn't work as it doesn't assign values to @.rows and @.pages that can
be accessed within the stored procedure.
Any suggestions?Use sp_executesql with output parameters:
DECLARE @.rows INT
DECLARE @.pages INT
DECLARE @.querystring NVARCHAR(300)
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC sp_executesql
@.querystring,
N'@.rows INT OUTPUT, @.pages INT output, @.perpage INT',
@.rows OUTPUT, @.pages OUTPUT, @.perpage
PRINT @.pages
PRINT @.rows
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:eNR78M7uFHA.3256@.TK2MSFTNGP09.phx.gbl...
> I have a dynamic SQL statement in which I need to assign values to
> variables.
> SELECT @.querystring = 'SELECT @.rows = COUNT(*)
> @.pages = COUNT(*) / @.perpage
> FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
> EXEC(@.querystring)
> This doesn't work as it doesn't assign values to @.rows and @.pages that can
> be accessed within the stored procedure.
> Any suggestions?
>

Assign T-SQL variables in dynamic SQL statement?

I have a dynamic SQL statement in which I need to assign values to
variables.
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC(@.querystring)
This doesn't work as it doesn't assign values to @.rows and @.pages that can
be accessed within the stored procedure.
Any suggestions?Use sp_executesql with output parameters:
DECLARE @.rows INT
DECLARE @.pages INT
DECLARE @.querystring NVARCHAR(300)
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC sp_executesql
@.querystring,
N'@.rows INT OUTPUT, @.pages INT output, @.perpage INT',
@.rows OUTPUT, @.pages OUTPUT, @.perpage
PRINT @.pages
PRINT @.rows
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:eNR78M7uFHA.3256@.TK2MSFTNGP09.phx.gbl...
> I have a dynamic SQL statement in which I need to assign values to
> variables.
> SELECT @.querystring = 'SELECT @.rows = COUNT(*)
> @.pages = COUNT(*) / @.perpage
> FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
> EXEC(@.querystring)
> This doesn't work as it doesn't assign values to @.rows and @.pages that can
> be accessed within the stored procedure.
> Any suggestions?
>

assign truncate rights to a user

i have a user who has delete rights on a table, but when i call truncate
table statement it says not enough permission.
how to assign truncate rightsIn 2000, you can't grant this. You need to be table owner or higher. In 2005
, you have some options.
From 2005 Books Online, TRUNCATE TABLE:
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permi
ssions default to the
table owner, members of the symin fixed server role, and the db_owner and
db_ddladmin fixed
database roles, and are not transferable. However, you can incorporate the T
RUNCATE TABLE statement
within a module, such as a stored procedure, and grant appropriate permissio
ns to the module using
the EXECUTE AS clause. For more information, see Using EXECUTE AS to Create
Custom Permission Sets.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vikram" <aa@.aa> wrote in message news:u2lvh83FGHA.1288@.TK2MSFTNGP09.phx.gbl...ed">
>i have a user who has delete rights on a table, but when i call truncate
> table statement it says not enough permission.
> how to assign truncate rights
>|||BOL says:
Permissions
TRUNCATE TABLE permissions default to the table owner, members of the
symin fixed server role, and the db_owner and db_ddladmin fixed database
roles, and are not transferable.
"Vikram" <aa@.aa> wrote in message
news:u2lvh83FGHA.1288@.TK2MSFTNGP09.phx.gbl...
>i have a user who has delete rights on a table, but when i call truncate
> table statement it says not enough permission.
> how to assign truncate rights
>

Assign multiple values using CASE in a Select Statement

Hello All,
I have a need to assign the values of the three variables in one select
statement. Currently, this is done using three
different Select statements :
Select @.Male = SMnemonic from sex where SName = 'Male'
Select @.Female = SMnemonic from sex where SName = 'Female'
Select @.Unknown = SMnemonic from sex where SName = 'Unknown'
I would like to replace with just one Select statement. I thought that this
is simple, however, I'm getting vague results :
NULL
(1 row(s) affected)
NULL
(1 row(s) affected)
Unknown Mnemonic is : U
(1 row(s) affected)
Thanks,
Gopi
CREATE TABLE [dbo].[Sex] (
[SCode] [char] (10) ,
[SName] [varchar] (50),
[SMnemonic] [char] (10)
)
GO
Select * from Sex order by SCode
SCode SName SMnemonic
-- ---- --
1 Male
M
2 Female
F
3 Unknown
U
4 Not Known
NK
Select SMnemonic from sex where SName = 'Male'
Select SMnemonic from sex where SName = 'Female'
Select SMnemonic from sex where SName = 'Unknown'
Declare @.Male char(10)
Declare @.Female char(10)
Declare @.Unknown char(10)
Set @.Male = 'Junk'
Set @.Female = 'Junk'
Set @.Unknown = 'Junk'
Select @.Male = CASE SName
WHEN 'Male' THEN SMnemonic
END ,
@.Unknown = CASE SName
WHEN 'Unknown' THEN SMnemonic
END ,
@.Female = CASE SName
WHEN 'Female' THEN SMnemonic
END
from Sex
WHERE SName IN ('Male','Female','Unknown')
Select 'Female Mnemonic is : ' + @.Female
Select 'Male Mnemonic is : ' + @.Male
Select 'Unknown Mnemonic is : ' + @.UnknownTry,
Select
@.Male = case when SName = 'Male' then SMnemonic else @.Male end,
@.Female = case when SName = 'Female' then SMnemonic else @.Female end,
@.Unknown = case when SName = 'Unknown' then SMnemonic else @.Unknown end
from
sex;
AMB
"rgn" wrote:

> Hello All,
> I have a need to assign the values of the three variables in one select
> statement. Currently, this is done using three
> different Select statements :
> Select @.Male = SMnemonic from sex where SName = 'Male'
> Select @.Female = SMnemonic from sex where SName = 'Female'
> Select @.Unknown = SMnemonic from sex where SName = 'Unknown'
> I would like to replace with just one Select statement. I thought that thi
s
> is simple, however, I'm getting vague results :
> --
> NULL
> (1 row(s) affected)
> --
> NULL
> (1 row(s) affected)
> --
> Unknown Mnemonic is : U
> (1 row(s) affected)
>
> Thanks,
> Gopi
> CREATE TABLE [dbo].[Sex] (
> [SCode] [char] (10) ,
> [SName] [varchar] (50),
> [SMnemonic] [char] (10)
> )
> GO
> Select * from Sex order by SCode
> SCode SName SMnemonic
> -- ---- --
> 1 Male
> M
> 2 Female
> F
> 3 Unknown
> U
> 4 Not Known
> NK
>
> Select SMnemonic from sex where SName = 'Male'
> Select SMnemonic from sex where SName = 'Female'
> Select SMnemonic from sex where SName = 'Unknown'
>
> Declare @.Male char(10)
> Declare @.Female char(10)
> Declare @.Unknown char(10)
> Set @.Male = 'Junk'
> Set @.Female = 'Junk'
> Set @.Unknown = 'Junk'
>
> Select @.Male = CASE SName
> WHEN 'Male' THEN SMnemonic
> END ,
> @.Unknown = CASE SName
> WHEN 'Unknown' THEN SMnemonic
> END ,
> @.Female = CASE SName
> WHEN 'Female' THEN SMnemonic
> END
> from Sex
> WHERE SName IN ('Male','Female','Unknown')
> Select 'Female Mnemonic is : ' + @.Female
> Select 'Male Mnemonic is : ' + @.Male
> Select 'Unknown Mnemonic is : ' + @.Unknown
>
>|||Alejadro,
Thanks a Million. I see the problem. Since ELSE part is missing it is
assigning NULLs and the reason why the last
variable, in this case Unknown, retains the value.
Gopi
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BB446702-10C6-47D3-91F7-3C6E9FD1D4FC@.microsoft.com...
> Try,
> Select
> @.Male = case when SName = 'Male' then SMnemonic else @.Male end,
> @.Female = case when SName = 'Female' then SMnemonic else @.Female end,
> @.Unknown = case when SName = 'Unknown' then SMnemonic else @.Unknown end
> from
> sex;
>
> AMB
> "rgn" wrote:
>

Assign a string to a variable

Hi
I'm using a varchar variable (@.Cadena) to store a select statement.
--
declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
set @.quincena = 24
set @.anio = 2004
set @.numeroempleado = 589
SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
WHEN 1 THEN ImpQuin01
WHEN 2 THEN ImpQuin02
WHEN 3 THEN ImpQuin03
WHEN 4 THEN ImpQuin04
WHEN 5 THEN ImpQuin05
WHEN 6 THEN ImpQuin06
WHEN 7 THEN ImpQuin07
WHEN 8 THEN ImpQuin08
WHEN 9 THEN ImpQuin09
WHEN 10 THEN ImpQuin10
WHEN 11 THEN ImpQuin11
WHEN 12 THEN ImpQuin12
WHEN 13 THEN ImpQuin13
WHEN 14 THEN ImpQuin14
WHEN 15 THEN ImpQuin15
WHEN 16 THEN ImpQuin16
WHEN 17 THEN ImpQuin17
WHEN 18 THEN ImpQuin18
WHEN 19 THEN ImpQuin19
WHEN 20 THEN ImpQuin20
WHEN 21 THEN ImpQuin21
WHEN 22 THEN ImpQuin22
WHEN 23 THEN ImpQuin23
WHEN 24 THEN ImpQuin24
END As SumaSDOS
FROM HisMovimientosQnal
WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = ' +
CAST(@.NumeroEmpleado AS varchar)
+ ' AND TipoIncidencia = ''SDOS'')'
EXEC (@.Cadena) --Execute the select statement
--
Till this point, the result is shown correctly.
I would like to store that result into another variable.
How can I do it?Gonzalo Torres wrote:
> Hi
> I'm using a varchar variable (@.Cadena) to store a select statement.
> --
> declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
> int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
> set @.quincena = 24
> set @.anio = 2004
> set @.numeroempleado = 589
> SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
> WHEN 1 THEN ImpQuin01
> WHEN 2 THEN ImpQuin02
> WHEN 3 THEN ImpQuin03
> WHEN 4 THEN ImpQuin04
> WHEN 5 THEN ImpQuin05
> WHEN 6 THEN ImpQuin06
> WHEN 7 THEN ImpQuin07
> WHEN 8 THEN ImpQuin08
> WHEN 9 THEN ImpQuin09
> WHEN 10 THEN ImpQuin10
> WHEN 11 THEN ImpQuin11
> WHEN 12 THEN ImpQuin12
> WHEN 13 THEN ImpQuin13
> WHEN 14 THEN ImpQuin14
> WHEN 15 THEN ImpQuin15
> WHEN 16 THEN ImpQuin16
> WHEN 17 THEN ImpQuin17
> WHEN 18 THEN ImpQuin18
> WHEN 19 THEN ImpQuin19
> WHEN 20 THEN ImpQuin20
> WHEN 21 THEN ImpQuin21
> WHEN 22 THEN ImpQuin22
> WHEN 23 THEN ImpQuin23
> WHEN 24 THEN ImpQuin24
> END As SumaSDOS
> FROM HisMovimientosQnal
> WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = '
+
> CAST(@.NumeroEmpleado AS varchar)
> + ' AND TipoIncidencia = ''SDOS'')'
> EXEC (@.Cadena) --Execute the select statement
> --
> Till this point, the result is shown correctly.
> I would like to store that result into another variable.
> How can I do it?
Exec @.variable =(@.Cadena)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Look up the topic sp_ExecuteSQL in SQL Server Books Online. It has some
examples which shows how to assign the results to an output variable.
Also, just by glancing through the code, it seems like you might have a
better/easier way of retrieving the results without using Dynamic SQL. But
then without further knowledge of your table structures & business rules, it
is hard to tell.
Anith|||I don't know your table structure, but this thing looks suspiciously
de-normalized...
Anyways, you might look into sp_executesql instead of EXEC. Safer and
shouldn't require all the CASTs, plus you can get a little performance boost
out of it since it compiles the query plan for parameterized queries. Check
it out in BOL.
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:O4nRqYCJFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi
> I'm using a varchar variable (@.Cadena) to store a select statement.
> --
> declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
> int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
> set @.quincena = 24
> set @.anio = 2004
> set @.numeroempleado = 589
> SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
> WHEN 1 THEN ImpQuin01
> WHEN 2 THEN ImpQuin02
> WHEN 3 THEN ImpQuin03
> WHEN 4 THEN ImpQuin04
> WHEN 5 THEN ImpQuin05
> WHEN 6 THEN ImpQuin06
> WHEN 7 THEN ImpQuin07
> WHEN 8 THEN ImpQuin08
> WHEN 9 THEN ImpQuin09
> WHEN 10 THEN ImpQuin10
> WHEN 11 THEN ImpQuin11
> WHEN 12 THEN ImpQuin12
> WHEN 13 THEN ImpQuin13
> WHEN 14 THEN ImpQuin14
> WHEN 15 THEN ImpQuin15
> WHEN 16 THEN ImpQuin16
> WHEN 17 THEN ImpQuin17
> WHEN 18 THEN ImpQuin18
> WHEN 19 THEN ImpQuin19
> WHEN 20 THEN ImpQuin20
> WHEN 21 THEN ImpQuin21
> WHEN 22 THEN ImpQuin22
> WHEN 23 THEN ImpQuin23
> WHEN 24 THEN ImpQuin24
> END As SumaSDOS
> FROM HisMovimientosQnal
> WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = ' +
> CAST(@.NumeroEmpleado AS varchar)
> + ' AND TipoIncidencia = ''SDOS'')'
> EXEC (@.Cadena) --Execute the select statement
> --
> Till this point, the result is shown correctly.
> I would like to store that result into another variable.
> How can I do it?
>

ASP.Net Unleashed Listing 12.16 Example

I am trying to get the following code to work but I keep getting an error.

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__titleauth__title__060DEAE8'. The conflict occurred in database 'pubs', table 'titleauthor', column 'title_id'.

Has anyone else experienced a problem with this example? Let me know what is wrong
with it.

Thanks,
Ralph

<%@. Page Language="VB" Debug="true" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SqlClient" %>
<script runat="server"
 Sub Page_Load
 Dim dstPubs As DataSet
 Dim conPubs As SqlConnection
 Dim dadTitles As SqlDataAdapter
 Dim dtblTitles As DataTable
 Dim drowTitle As DataRow
 Dim objCommandBuilder As New SqlCommandBuilder
 ' Grab Titles Table
 dstPubs = New DataSet()
 conPubs = New SqlConnection( "Server='(local)';Database=Pubs;trusted_connection=true" )
 dadTitles = New SqlDataAdapter( "Select * from Titles", conPubs )
 dadTitles.Fill( dstPubs, "Titles" )
 dtblTitles = dstPubs.Tables( "Titles" )
 ' Display Original Titles Table
 dgrdOriginalTitles.DataSource = dstPubs
 dgrdOriginalTitles.DataBind()
 ' Add a Row
 drowTitle = dtblTitles.NewRow()
 drowTitle( "Title_id" ) = "xxxx"
 drowTitle( "Title" ) = "ASP.NET Unleashed"
 drowTitle( "Price" ) = 1200.00
 drowTitle( "Type" ) = "Mystery"
 drowTitle( "PubDate" ) = #12/25/1966#
 dtblTitles.Rows.Add( drowTitle )
 ' Delete the First Row
 dtblTitles.Rows( 0 ).Delete()
 ' Double the price of the Second Row
 drowTitle = dtblTitles.Rows( 2 )
 drowTitle( "Price" ) *= 2
 ' Generate the SQL Commands
 objCommandBuilder = New SqlCommandBuilder( dadTitles )
 ' Update Titles Table
 dadTitles.Update( dstPubs, "Titles" )
 ' Display New Titles Table
 dgrdNewTitles.DataSource = dstPubs
 dgrdNewTitles.DataBind()
 End Sub
</script>
<html>
<head>
 <title>UpdateDataSet</title>
</head>
<body>
 <h2>Original Titles Table
 </h2>
 <asp:DataGrid id="dgrdOriginalTitles" Runat="Server"></asp:DataGrid>
 <h2>New Titles Table
 </h2>
 <asp:DataGrid id="dgrdNewTitles" Runat="Server"></asp:DataGrid>
</body>
</html>

change this line

dgrdOriginalTitles.DataSource = dstPubs

to this

dgrdOriginalTitles.DataSource = dtblTitles

see if that works|||Thanks for the suggestion. I tried something else...I remarked out the following code and then it worked fine.

Thanks,
RT

' Update Titles Table
dadTitles.Update( dstPubs, "Titles" )

ASP.NET SQL CONTAINS statement

I have an SQL db that I need to be able to search and display. I haveabout seven different columns and would like results to be returned toa datagrid based upon the search criteria entered by the user.
How do I construct a SELECT statement such as below using the ASP.NET 1.1+?
SELECT * FROM db.table WHERE CONTAINS ("searchable term")
I can get a column searched and return the results already, but if Imodify to search all columns and use the CONTAINS it will fail. Isthere a way to do this easily?
Thanks,
TRKneller

Your query is failing because you are using CONTAINS Microsoft Proprietry FULL TEXT search predicate when you need to use LIKE which is ANSI SQL used to search table column based data. Full Text is used for Text and NText data but it is an add on to SQL Server dependent on Microsoft Search Service and the Catalog must be populated to get search results. SQL Server creates an Arithmetic Pointer to Text and NText data in your file system. Run a search for LIKE and FULL Text in the BOL(books online). Hope this helps.