Re: STORED PROCEDURE is not working HELP
From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 08/06/04
- Next message: William \(Bill\) Vaughn: "Re: CR questions, and TY to Bill"
- Previous message: William \(Bill\) Vaughn: "Re: Autosproc, DAL, Data Access Application Blocks, etc."
- In reply to: Joriz: "STORED PROCEDURE is not working HELP"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 5 Aug 2004 19:34:02 -0700
1) Turn on the SQL Profiler to see what's getting executed.
2) I would setup the parameter as a DateTime or SmallDateTime (depending on
how it's defined in the SP).
3) It would help to see the SP code. What tables does it create? If they are
#temp tables, consider that these are dropped after the SP that creates them
ends.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Joriz" <google@joriz.is-a-geek.net> wrote in message
news:7d49ea6b.0408041345.7ae4a695@posting.google.com...
> I have this code in my btnclick event.
>
> Dim cnn As New SqlConnection(Constants.SQLConnectionString)
> Dim cmd As New SqlCommand
>
>
> Dim cmd As New SqlCommand("spAverage", cnn)
> cnn.Open()
>
>
>
> cmd.CommandType = CommandType.StoredProcedure
> Dim prm1 As SqlParameter = cmd.Parameters.Add("@sdate",
> txtsdate.Text)
> Dim prm2 As SqlParameter = cmd.Parameters.Add("@edate",
> txtedate.Text)
> cmd.ExecuteNonQuery(CommandBehavior.CloseConnection)
>
> The spAverage does not return any resultset, but it does create a new
> table with the all the rows based on the date range parameter that i
> passed.
>
> but right after i execute the sp and i check the table that was
> created. its empty..altho i know there should be rows in there.
>
> anybody have this problem as well.
> Please HELP
>
>
> here's my sp.
>
> CREATE PROCEDURE spAverage @sdate NVARCHAR(10),@edate NVARCHAR(10)
> AS
>
> SET NOCOUNT ON
>
> SET @sdate = CONVERT(NVARCHAR(8),CAST(@sdate AS DATETIME),112)
> SET @edate = CONVERT(NVARCHAR(8),CAST(@edate AS DATETIME),112)
>
> IF not object_id('tempdb..#t1') is null
> DROP TABLE #t1
>
> SELECT r.qid,svalue1 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO #T1
> FROM groupq g
> INNER JOIN questionaires q ON q.groupid = g.groupid
> INNER JOIN result r ON r.qid = q.qid
> INNER JOIN participant p ON p.participantid=r.participantid
> INNER JOIN lkcourse l ON l.courseid= p.levelid
> WHERE q.ratetype IN (1,2) and surveyvalue is not null and
> surveyvalue <> 0 AND l.grp IN ('G')
> AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
> @edate)
> GROUP BY r.qid
> order by r.qid
>
> if not object_id('tempdb..#t2') is null
> drop table #t2
>
> select qid2=r.qid,svalue2 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO
> #T2
> FROM groupq g
> INNER JOIN questionaires q ON q.groupid = g.groupid
> INNER JOIN result r ON r.qid = q.qid
> INNER JOIN participant p ON p.participantid=r.participantid
> INNER JOIN lkcourse l ON l.courseid= p.levelid
> WHERE q.ratetype IN (1,2) and surveyvalue is not null and
> surveyvalue <> 0 AND l.grp IN ('M')
> AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
> @edate)
> GROUP BY r.qid
> order by r.qid
> if not object_id('tempdb..#t3') is null
> drop table #t3
>
> select qid3=r.qid,svalue3 = AVG(CAST(r.surveyvalue AS DECIMAL)) INTO
> #T3
> FROM groupq g
> INNER JOIN questionaires q ON q.groupid = g.groupid
> INNER JOIN result r ON r.qid = q.qid
> INNER JOIN participant p ON p.participantid=r.participantid
> INNER JOIN lkcourse l ON l.courseid= p.levelid
> WHERE q.ratetype IN (1,2) and surveyvalue is not null and
> surveyvalue <> 0 AND l.grp IN ('P')
> AND (CONVERT(NVARCHAR(8),p.EntryDate,112) BETWEEN @sdate AND
> @edate)
> GROUP BY r.qid
> order by r.qid
>
> if not object_id('tempdb..#t4') is null
> drop table #t4
>
> SELECT * INTO #T4 FROM #T1 t1
> FULL JOIN #T2 t2 ON t2.qid2 = t1.qid
> FULL JOIN #T3 t3 ON t3.qid3 = t1.qid
>
> UPDATE #T4 SET qid=ISNULL(qid2,qid3) WHERE ISNULL(qid,'')=''
>
> if EXISTS(select [name] from sysobjects where [name] = 'tpAverage')
> DROP TABLE tpAverage
>
> select tp.topicid,tp.topicdesc,g.qnum,g.groupdesc,q.qdesc,
> t4.qid,t4.svalue1,t4.svalue2,t4.svalue3
> INTO tpAverage
> FROM #t4 t4
> INNER JOIN questionaires q ON q.qid=t4.qid
> INNER JOIN groupq g ON g.groupid = q.groupid
> INNER JOIN topics tp ON tp.topicid = g.topicid
> ORDER BY tp.topicid, qnum, t4.qid
>
> -- SELECT * FROM tpAverage
> SET NOCOUNT OFF
> GO
- Next message: William \(Bill\) Vaughn: "Re: CR questions, and TY to Bill"
- Previous message: William \(Bill\) Vaughn: "Re: Autosproc, DAL, Data Access Application Blocks, etc."
- In reply to: Joriz: "STORED PROCEDURE is not working HELP"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
