Problems Executing A SELECT Inside A TRAN Against Other Computer

EXEC Inside CASE Inside SELECT
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here.  I'm looking for something more elegant.
 
@val1 and @val2 are passed in
 
 
CREATE TABLE #TEMP(
 tempid INT IDENTITY (1,1) NOT NULL,
 myint INT NOT NULL,
 mybool BIT NOT NULL
)
 
INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2

 
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1

 
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
 
Any suggestions?

View Replies !

---

Executing SP Inside SP Dynamically
I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.

Exec sp_executesql N’exec procedurename {parameterlist}’, N’{parameter declaration}’, Parametervalues


Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.

Please help me.

Thanks!

View Replies !

---

Executing BCP Utility Inside Sp....
Hi ,

My reuierement is to execute a bcp to write the query output to a flat file. I am using the following syntax...

exec xp_cmdshell 'bcp "SELECT top 50 * tbl_xyz" queryout tbl_xyz_output.txt -c -S abc -U x -P x'

But I am getting the following error....

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CTLIB Message: - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.
Establishing connection failed.
NULL


This bcp executes perfect if I am going to call from command prmomt.

Can anyone please help me in this....

Thanks in Advance.

-Mohit.

View Replies !

---

Executing A Query Inside A Procedure
Hello,
I have written a procedure and I have a variable that has been declared as a varchar. For example:
@test = 'select * from table_a'
I want to able to execute this @test varible. Any help will be greatly appreciated.
Thank you

View Replies !

---

Executing A Query Inside A Procedure
Hello,
I have written a procedure and I have a variable that has been declared as a varchar. For example:
@test = 'select * from table_a'
I want to able to execute this @test varible. Any help will be greatly appreciated.
Thank you

View Replies !

---

Executing A Variable Inside A Stored Procedure
Hello :) I need to do something like this:
 
CREATE PROCEDURE SelectCostumers @name varchar(100)
Declare @SQL = "SELECT Id, Name FROM Costumers"
AS
IF (@name IS NULL)
@SQL
ELSE
@SQL += "WHERE Name LIKE @name"
 
See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.
 
Thank you

View Replies !

---

Begin Tran, Commit Tran ( Does It Work With Sp And Normal Query)
declare @error_status int
@id int
select @id =3
begin tran
update table A <------ here
set title ='Doctor'
where id = @id
if (@error_status <>0) -- if error exists
begin
rollback tran
insert into tbl_check (id,status,date)
values (@id,'process #1,Not complete',getdate())
return
end
else
begin
commit tran
insert into tbl_check (id,status,date)instead of the sql statement which is located at <---- above.

thanks for you input

values (@id,'process #1,Complete',getdate())
end


HERE IS MY QUESTION, is this code correct, if so can I replace the update statement with this execute p_update tbl_a ( this procedure contains the update sql)... the whole point is to see if I can use the execute p_update
regards
Ali

View Replies !

---

Usage Of @@error In Begin Tran, Commit Tran
hi, I have the following tables
/* CREATE TABLE */
create table a(id int , title varchar(30))
insert a values(1,'pilot')
insert a values(2,'manager')
insert a values(3,'student')
create table tbl_check (id int,status varchar(50),date datetime )

then ran this procedure :

declare @error_status int ,
@id int
select @id =4
begin tran
update a
set title ='PILOT2'
where ID =@ID
if (@@error <> 0) --or @id not in(select id from a) -- if error exists
begin
rollback tran
insert into tbl_check (id,status,date)
values (@id,'process #1,Not complete',getdate())
end
ELSE
begin
commit tran
insert into tbl_check (id,status,date)
values (@id,'process #1,Complete',getdate())
end
NOW , what kind of error is defined for @@error.... here are my guess, and I am not sure if they are right
if there are no table named A
if there are no id = 4
if there is no record to update in table A

so @@error will hold >0 if either table name A or id # 4 is not in the table, or no records is in the table... so the update will not occur, which means that the update sql will not execute, as a result an error will occur and @@error will be > 0... therefore, roll back tran will be executed and the second statement will be executed ... which is
begin
rollback tran
insert into tbl_check (id,status,date)
values (@id,'process #1,Not complete',getdate())
end

This is what I know, Please If I am correct tell me, if not tell me the right thing... thank you for enlighting me with your experience

View Replies !

---

Logistics Of Begin Tran And Rollback Tran
I am running an Execute SQL task that does a Begin Tran,  then the next task in the sequence is a data task which imports a XML file into two tables. If i doo a Rollback Tran only one of the two tables is rolled back.

Is it possible to have both tables rolled back from one Begin tran command or do i need to split the datatasl into two and treat each import as a seperate issue ?

The connection is set to  retainsameconnection

thanks

View Replies !

---

Begin Tran, Commit Tran
 

Hi,
 
I want to rollback my t-sql if it encounters an error. I wrote this code:
 
begin tran mytrans;
insert into table1 values (1, 'test');
insert into table1 values (1, 'jsaureouwrolsjflseorwurw');   -- it will encounter error here since max value to be inputted is 10
commit tran mytrans;
 
I forced my insert to have an error by putting a value that exceeds the data size. However, I didn't do any rollback. Anything i missed out?
 
cherriesh

View Replies !

---

How Do I Imbed A Select Inside A Select
I need a select that gets a value and than appends another value if the criteria is met otherwise nothing is appended.

The statement has a select with an imbedded select and when I execute it I get the error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Thia is a crude sample of the statement

SELECT ID + ( select * from tableB where TableB = 0 ) as result1
FROM TableB

Why am I getting this error and how do I fix the statement?
thanks

View Replies !

---

Using IF Inside SELECT ?
Is there possibility to use IF conditions inside SELECT statements?For example, can i write something like this:CREATE PROCEDURE [search](@OPTION int,@KEYWORD nvarchar(40))ASBEGINSELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)THEN (OR description LIKE @KEYWORD)ENDor am i limited to this:....BEGINIF @OPTION = 1SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE@KEYWORDELSESELECT id FROM projects WHERE title LIKE @KEYWORDEND

View Replies !

---

Using A While Inside A Select Statement
 

Hi All,
Can we use the while loop inside a select statement? Meaning, something like this:
 



Code Block
SELECT DATE,
WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23
(SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) )
FROM SC_DATEDIMENSION_TABLE
 
 




What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right.
 
Hope the question is clear.
 
Thanks a lot.
 
Mannu.

View Replies !

---

SET Inside Of SELECT Statement
How can I do this

Select id
set @temp = id
from tblexample

I want to do something, on each row output, with @temp.

View Replies !

---

Select Statement Inside UDf
iam trying to rerieve a certain value from one table
and i want to use that vaue inside a UDF
iam usinf a table valued function as i have to retireve no of values
Can i do something like this to retrieve the value

SET @Value=Select Value from Table WHERE xyz='some no.'
as this value is being calculated by some other fucntion and now this funcation has to use this at runtime.

View Replies !

---

Counter Inside Select Statement?
Hi, can you add a counter inside a select statement to get a unique id line of the rows?
In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order.
This is the normal way:
SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @topicID) ... some more where/order by statements
This is what i want:
DECLARE @tempCounter bigintSET @tempCounter = 0SELECT @tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @topicID)... some more where/order by statements and at the end.. (SELECT @tempCounter = @tempCounter + 1)
Anyone know if this can be done?

View Replies !

---

Function Inside A Select Statement
Can I write a function inside a Select statement in sql server 7.0
If so HOW ?

Manish Mehta

View Replies !

---

Declare Inside Select Statement?
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query.

So! Here's the statement that works:

======================
DECLARE @fixeddate datetime
DECLARE @CVG_ELECT char(1)
DECLARE @Effdt datetime
DECLARE EFFDTS CURSOR FOR
SELECT Z.EFFDT, COVERAGE_ELECT
FROM PS_LIFE_ADD_BEN Z
WHERE Z.EMPLID = '1000'
AND Z.EFFDT <=
GETDATE()
AND Z.PLAN_TYPE = '20'
ORDER BY Z.EFFDT DESC
OPEN EFFDTS
FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT
WHILE @@FETCH_STATUS = 0
BEGIN
if @CVG_ELECT <> 'E'
break
ELSE
SET @fixeddate = @Effdt
FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT
END

CLOSE EFFDTS
DEALLOCATE EFFDTS
PRINT @fixeddate

======================
If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea).

Is it possible to encapsulate this inside a select statement?

View Replies !

---

Select Case Inside Sql Statement ?
Code:


function findingcinemaid(nameofthecinema)

findcinemaid = "select cinemasid from cinemas" &_
" where brand = 'tgv' and cinemaplace2 like '"&nameofthecinema&"'"
set cinemaidfound = objconndb.execute (findcinemaid)

end function

select case foreachcinema

case 0

cinemaname = "ONE UTAMA"
findingcinemaid(cinemaname)



case 1


cinemaname = "MINES"
findingcinemaid(cinemaname)



case 2


cinemaname = "SEREMBAN 2"
findingcinemaid(cinemaname)



case 3


cinemaname = "KINTA CITY"
findingcinemaid(cinemaname)


case 4


cinemaname = "BUKIT RAJA"
findingcinemaid(cinemaname)


case 5

cinemaname = "TEBRAU CITY"
findingcinemaid(cinemaname)

case 6

cinemaname = "SUNWAY PYRAMID"
findingcinemaid(cinemaname)



case 7

cinemaname = "SURIA KLCC"
findingcinemaid(cinemaname)


end select




any possible way I can merge this select case statement with the sql statement ? I try if else but too many code , defeating the original purpose of simplfying it

View Replies !

---

Select The CheckBox Inside The DataGridView
Hi,
 
Can anybody help me fetch "True" & "False" value from a CheckBox inside a DataGridView row.
 
I tried the following code:-
 
But the cell.Selected doesnot take the correct value.
 

objGrid = (System.Windows.Forms.DataGridView)controlObj[0];

foreach (DataGridViewRow row in objGrid.Rows)

{

DataGridViewCheckBoxCell cell = row.Cells[0] as DataGridViewCheckBoxCell;



if (cell.Value != cell.FalseValue)



{

if (cell.Selected == true)

{

///Some Code;
}

}



}
 
 
Kindly Help ASAP
Thanks

View Replies !

---

Select Inside Update Query?
Is it possible for me to do something like

update table1 SET var1=something,var2=something2
from table1
(SELECT * from table2) as newtable
where newtable.field1=acondition

View Replies !

---

Checking For @@ERROR After A SELECT Inside A Transaction?
Is it normal practice to check for @@ERROR  after a SELECT statement that retrieves data from a table OR we should only check for @@ERROR after a DELETE/INSERT/UPDATE type of statement? The SQL statement is inside a transaction.

View Replies !

---

Subquery With Multiple Rows Inside SELECT
Hi there,
I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results.
Here is my basic structure:

Table: Profiles
Fields: Id, ProfileName

Table: Groups
Fields: Id, GroupName, ProfileId

I then need to return something like this:

ProfileName,Groups
"Joe Soap","Group1, Group2, Group3"

Does anyone know how this can be done?
Thanks!

View Replies !

---

Newbe Question: Calling Function Inside Select
Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.

View Replies !

---

SELECT Query Stmt Inside Stored Procedure
Friends,

What are the possible usuages of a SELECT query stmt inside a stored procedure ??

How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ??

can any one throw some lite on this ..

Thanks,
SqlPgmr

View Replies !

---

Stored Procedure Output Parameter Inside Select...
Does anyone know how can I (or can I) use a stored procedure output parameter(s) inside Select statement. For example

Select abc, cde, 'xyz' = Case
When 'aaa' then {output parameter of my stored procedure with 'aaa' as input parameter}
When 'bbb' then {output parameter of my stored procedure with 'bbb' as input parameter}
end
from MyTable

Thanks
Arcady

View Replies !

---

Executing A Select Statement
Hello All!!
Below is a part of my code. I need to find out if I could execute the below statement.

select @string_sortorder = 'select max(sortorder) from geo_lineobj where pipeid = ' + str(@geo_pipeid)
select @string_sortorder
exec @string_sortorder @max_sortorder

When I select the @max_sortorder the value I'm getting is a null. The sortorder is an int datatype and the @geo_pipeid is also an int datatype.
I need to to get the @max_sortorder to continue my coding. Does anyone know why I'm getting a null @max_sortorder?

Any suggestion would be of great help.
Thank you

View Replies !

---

How To Use A Stored Procedure Inside Select Query (sql Server Version 8)
 

Hi,
      Please help me in this problem...
       i am new to sql server..
       i am using sql server version 8...(doesnot support function with retun values..)
       so i have created a procedure...
        -----------procedure------------------(to find next monday after 6 months)-------------------
[code]
create proc next_Monday ( @myDate DATETIME ) 
as
BEGIN
 set @myDate = dateadd(mm, 6, @myDate) 
 while datepart(dw,@myDate) <> 2
  begin 
   set @myDate = dateadd(dd, 1, @myDate) 
  end 
   select @myDate
 end
go
[/code]
       --------------------------------------------------------
    i can able to execute this procedure separately.... working well...
    but don't know how to call it inside another query....
  the following throws error....
select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster 
   please help me... how to fix this problem...
 
  

View Replies !

---

SqlDataSource - Executing Select Statement
I have multiple SqlDataSources on a page.  I only want them to execute the select query when the user performs a certain action.  Is this possible?  Or do they all execute as soon as the page is loaded?

View Replies !

---

Executing Stored Procedure In A Select
i wanna execute a stored procedure in a select and use its return type
i.e

select name from table1 where id = sp 1,1

i executed it and occurred an error

help me pls

View Replies !

---

How Can I Fill Data In Textboxes From Sql Databases But Two Different Tables When I Select A Name That Is Inside A Dropdownlist
HI
I need help 
how can i fill data in textboxes from sql databases but two different tables when i select a name that is inside a dropdownlist
my controls are as follows
<asp:DropDownList ID="ddl" runat="server" DataSourceID="SqlDataSource13" DataTextField="fullname" DataValueField="fullname">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource13" runat="server" ConnectionString="<%$ ConnectionStrings:NPI Employee MasterConnectionString2 %>"
SelectCommand="SELECT [FirstName]+' '+ [Surname] as fullname FROM [Employee]&#13;&#10;where CurrentEmployee_YN=1&#13;&#10;order by FirstName "></asp:SqlDataSource><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 100%;
border-collapse: collapse; height: 32px; visibility: hidden;" id="table0">
<tr>
<td style="width: 159px; visibility: hidden;">
</td>
<td style="width: 170px">
</td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Order No：</strong></td>
<td bgcolor="#eeeddb" style="width: 26%; height: 25px">
&nbsp;<asp:Label ID="OrderNo" runat="server" Width="104px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Account No：</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="AccountNo" runat="Server" MaxLength="10" Width="130px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="AccountNo"
Display="Static" ErrorMessage="Enter Acc No." Text="*"></asp:RequiredFieldValidator></td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Today's Date:</strong></td>
<td>
<asp:Label ID="Label1" runat="server" Text="Label" Width="200px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Travel Consultant：</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="Consultant" runat="Server" MaxLength="30" Width="128px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="Consultant"
Display="Static" ErrorMessage="Enter Travel Consultant." Text="*"></asp:RequiredFieldValidator></td>
</tr>
</table>
<center>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
</center>
<center>
&nbsp;</center><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 80%;
border-collapse: collapse; height: 32px; display: block; visibility: hidden;" id="table2">
<tr>
<td align="center" bgcolor="#ffcc33" colspan="3" style="width: 90%; height: 29px">
<font color="#000000" size="5">Enter Passenger(s) Details</font></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Surname：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Surname" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
&nbsp; &nbsp;<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="Surname" Display="Static" ErrorMessage="Enter Surname." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 20px">
<strong>
Name：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 20px">
<asp:TextBox ID="Name" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Name"
Display="Static" ErrorMessage="Enter Name." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Initials：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Initials" runat="Server" MaxLength="5" Width="148px"></asp:TextBox>
 
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Title：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:DropDownList ID="DropDownList1" runat="server" Width="156px">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="Mr"></asp:ListItem>
<asp:ListItem Value="Mrs"></asp:ListItem>
<asp:ListItem Value="Ms"></asp:ListItem>
<asp:ListItem Value="Dr"></asp:ListItem>
<asp:ListItem Value="Prof"></asp:ListItem>
<asp:ListItem Value="Min"></asp:ListItem>
<asp:ListItem Value="Other"></asp:ListItem>
</asp:DropDownList>&nbsp;
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="Dropdownlist1"
Display="Static" ErrorMessage="Select Title." Text="*" Width="20px"></asp:RequiredFieldValidator>
</td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Department</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Department" runat="server"></asp:TextBox></td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Cost Centre</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="CostCentre" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Tel：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Tel" runat="SERVER" maxlength="15" name="Tel" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
&nbsp;&nbsp;
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Fax：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Fax" runat="SERVER" maxlength="15" name="Fax" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
</table>
cost centre and department are from cost table
and the rest are from employee table

View Replies !

---

VIEWs - Can It Know Wether A SELECT, UPDATE, Etc Is Executing On It
VIEW behaviour depending on what operation is run on it.

VIEW syntax is CREATE VIEW xx AS SELECT ... It would be very convinient to know wether a select, update, insert or delete is the operation executed on it so the view could take action accordingly, ie
CREATE VIEW xxx AS SELECT ..
CASE UPDATE THEN do_this
CASE SELECT THEN do_that etc.
Does anyone know if this is possible?

View Replies !

---

EXECUTING STORE PROCEDURE IN SELECT STATMENT
hI
DOES ANY BODY KNOW HOW TO EXECUTE STORE PROCEDURE IN SELECT STATMENT

THANKS
jk

View Replies !

---

Error Executing Dynamic Select Query
Hi,
 
i have problem executing the dynamic query.
 
I have straight forward query as below and works fine
 

declare @count bigint

declare @varcount varchar(max)

set @varcount ='5'

If convert(bigint, @varcount) <> 4

print ' not 4 '

else

print 'Its 4'

 
 
Here is my dynamic query. The number of records (@No ) and the table name ( @table ) will be available for me as parameters to my stoped proc
 
 

declare @count bigint

declare @varcount varchar(max)

declare @tempTable varchar(max)

declare @vsSql varchar(max)

declare @No bigint

set @No = 5

set @table = 'sam'

set @varcount = ''

select @vsSql = 'declare @varcount varchar(max); select @varcount = count(*) from ' + @table + '; If convert(bigint,@varcount) <> ' + @No + ' raiserror(' +'mismatch, 11,1' +')'

 
 
When executed it throws the follwing error
 

Msg 8114, Level 16, State 5, Line 10

Error converting data type varchar to bigint.

 
 
Can anyone point out what to change in the query to work
 
~mohan

View Replies !

---

Executing Stored Proc On Multiple Rows From Select
I have a couple of stored procs I'd like to execute sequentially building the parameter list from the result of a SELECT statement.

Each proc takes a large amount of parameters, and each proc takes several actions (a number of INSERTs and a number of UPDATEs)

The procs themselves are pretty bulky and probably somewhat error prone. For that reason, I'd like to avoid modifying them, potentially having to spend alot of time debugging there.

I'd like to basically have it work like this, but this doesn't work out syntactically:

SELECT
exec sp1 f1,f2,f3,f4...
exec sp2 f2,f4,f5,f6...
FROM
SomeTable

I first thought the easiest parallel would be to move the sps to UDFs and use the UDFs, but when I tried this it failed due to my trying to INSERT/UPDATE from a UDF.

I've read several posts like mine, and the suggestions are usually to convert the proc to a UDF (like I tried, but I need INSERT/UPDATE), or to rework the proc involved. Here I need 2 procs to exec on the SELECT data, and it would probably be much more of a hassle to rework these procs than the performance penalty the cursor use will involve.

That said, my thought is to use a cursor. I understand how to use the cursor, and I'm pretty sure this will work just fine. Before implementing that though, I wanted to see if anyone had any alternate suggestions since cursors seem to be the "unnatural" way of doing most things.

View Replies !

---

Do GetDate() Inside SQL Server OR Do System.DateTime.Now Inside Application ?
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.

View Replies !

---

How To Write Select Statement Inside CASE Statement ?
Hello friends,
      I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
    CASE E.EmployeeType
        WHEN 1 THEN
            select * from Tbl1
        WHEN 2 THEN
            select * from Tbl2
        WHEN 3 THEN
            select * from Tbl3
    END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View Replies !

---

How To Know Whether A Computer Is A Sql Server According To The Name Of Computer?
Thanks a lot

View Replies !

---

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu
 

Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png


Any ideas for this issue?

Thanks a lot

View Replies !

---

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
 
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

 

View Replies !

---

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View Replies !

---

Different Results When Executing From .NET Component Compare To Executing From SQL Management Studio
Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys

View Replies !

---

Tran Log
To All:

On my SQL 6.5 box, I have a corrupt Tran log. I do not use my Tran log but now I am getting an 1105 error, that the log is full. I run Dump tran with no log but it does not work. I cannot perform any other function without getting the 1105 error. Now I tried to reboot and now it is hanging during reboot. It is hanging while checking the partition where the tran log resides. I went in to VGA Mode. Any ideas would be appreciated.

Many thanks,

Kelly

View Replies !

---

Tran Log
I have a transaction log that is over f gig in size....what can be done with this..and what are the pros and cons if I delete it...also how can I keep this from getting that big in the future. Thanks!

View Replies !

---

Tran Log Problems Please Help
Help. I have a database with high transaction rates. THe log is 300 mbs. No matter what i do I cannot get it below 64%. I have dumped and trucated the log yet it will not budge. Being friday and it being a time card application this is my heaviest transaction day. Please help

View Replies !

---

Tran Log Space Used ????
Hi There,
How do I find the space used for the tran log of the db. sp_spaceused gives the space used for the complete database. but I need the space used for a tran log alone.
Thanks in advance.
pete

View Replies !

---

Tran Log Growth
Does this seem right? We have our transaction logs set to "Truncate Log on Checkpoint" and they still grow over 1GB. Is it possible that one transaction (to a checkpoint) generates this much logged information? Will transaction log backups every 5-10 minutes help me out better or is this just a poorly written application?

Thanks!

View Replies !

---

Contents Of The Tran Log
All,

Can everyone tell me how I can view the contents of a transaction log in SQL Server 7.

Many Thanks
Mathew hayward

View Replies !

---